判断两个字符串是否存在相同的内容代码,MSSQL代码库,德仔网

代码语言

代码分类

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

作者: / 发布于2018/5/12/ 972

-- =============================================
-- 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

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label