sql 判断两个字符串是否存在相同的内容

 

 

-- =============================================

-- Author:  wuchaorang

-- Create date: 2010-12-22

-- Description: 判斷兩個字符串是否存在相同的內容(查找@newSplit中是否有相同的字符串存在于@orginSplit中)

-- Debug:select  dbo.fun_charindex('a,b,c,d','d',',')  or select  dbo.fun_charindex('a,b,c','b,',',')

--返回:           存在返回1,否則返回0

-- =============================================

ALTER function fun_charindex

(

   @orginSplit  varchar(4000),--原始值

   @newSplit varchar(4000),--需要判斷的值

   @speaterString varchar(20)--分隔符

)

returns smallint

as

begin

   declare @val smallint,@i int,@helpSplit varchar(4000)

   set @val=0

   set @newSplit=rtrim(ltrim(@newSplit))

   set @speaterString=rtrim(ltrim(@speaterString))

   set @orginSplit=rtrim(ltrim(@orginSplit))

 if left(@orginSplit,len(@speaterString))<>@speaterString

    set  @orginSplit=@speaterString+@orginSplit

 if right(@orginSplit,len(@speaterString))<>@speaterString

    set  @orginSplit=@orginSplit+@speaterString

if @newSplit=''

   return @val

 if charindex(@speaterString,@newSplit)=0  

   begin

    --或者改成set  @helpSplit=replace(@orginSplit,@speaterString+@newSplit+@speaterString,'')

-- if len(@speaterString+@newSplit+@speaterString)+len(@helpSplit)=len(@orginSplit)

set  @helpSplit=@speaterString+@newSplit+@speaterString

if patindex('%'+@helpSplit+'%',@orginSplit)>0

  set @val=1

   end

 else

    begin

   if right(@newSplit,len(@speaterString))<>@speaterString

  set @newSplit=@newSplit+@speaterString

set @i=charindex(@speaterString,@newSplit)

while @i>0

  begin

     if rtrim(ltrim(left(@newSplit,@i-1)))=''

begin

 set  @newSplit=SUBSTRING(@newSplit,@i+1,LEN(@newSplit)-@i)

 set @i=CHARINDEX(@speaterString,@newSplit)

 continue

end

set @helpSplit=@speaterString+rtrim(ltrim(left(@newSplit,@i-1)))+@speaterString

--或者改成  len(@helpSplit)+len(replace(@orginSplit,@helpSplit,''))=len(@orginSplit)

 if patindex('%'+@helpSplit+'%',@orginSplit)>0

   begin

 set @val=1

 break

end

 set  @newSplit=SUBSTRING(@newSplit,@i+1,LEN(@newSplit)-@i)

 set @i=CHARINDEX(@speaterString,@newSplit)

  end

    end

return @val

end

 

 

实例：

select  dbo.fun_charindex('aa,bb,cc,dd','b,b,cc',',') 存在则返回1，否则返回0