SQL判断两个带有分隔符的字符串是否重叠

SQL Script:

ALTER FUNCTION [dbo].[ufn_IsOverlap]
(
@Value1 NVARCHAR(MAX),
@Value2 NVARCHAR(MAX),
@SPLIT VARCHAR(1)=',' --预设分隔符
) RETURNS VARCHAR(10) --Y:有重叠，N:不重叠
AS
BEGIN

/************************************************

程序说明：SQL判断两个带有分隔符的字符串是否重叠

--测试案例
print dbo.ufn_IsOverlap('','',',')
print dbo.ufn_IsOverlap('','a',',')
print dbo.ufn_IsOverlap('a','a,c',',')
print dbo.ufn_IsOverlap('a,c','a',',')
print dbo.ufn_IsOverlap('a','a',',')
print dbo.ufn_IsOverlap('a,b,c','d,c,f',',')
print dbo.ufn_IsOverlap('a,b,c,d,e,f,g','z,x,c',',')
print dbo.ufn_IsOverlap(',d,','a,b,c,d,e,f,g',',')
print dbo.ufn_IsOverlap('1,2,3,4,5,6,7','a,b,c,d,e,f,g',',')


print CHARINDEX('1a','bab')

************************************************/

--两个参数有空值，返回N
IF ISNULL(@Value1,'')='' OR ISNULL(@Value2,'')='' RETURN 'N'

DECLARE @tmp NVARCHAR(2000)
DECLARE @Return CHAR(1)--Y/N
SELECT @Return='N'

--基于算法优化，下面循环长度小的数据
IF LEN(@Value1)>LEN(@Value2)
BEGIN
DECLARE @Swap NVARCHAR(max)
SELECT @Swap=@Value2
SELECT @Value2=@Value1
SELECT @Value1=@Swap
END;

--循环@Value1
WHILE(CHARINDEX(@SPLIT,@Value1)<>0)
BEGIN
SET @tmp=''
SET @tmp=RTRIM(LTRIM(SUBSTRING(@Value1,1,CHARINDEX(@SPLIT,@Value1)-1)))

--判断与@Value2是否重叠
IF CHARINDEX(','+@tmp+',',','+@Value2+',')<>0
BEGIN
SELECT @Return='Y'
BREAK;
END;

SET @Value1=STUFF(@Value1,1,CHARINDEX(@SPLIT,@Value1),'')
END

--处理无分隔符的数据(或者循环的最后一个数据)
IF @Value1<>'' AND CHARINDEX(','+@Value1+',',','+@Value2+',')<>0
SELECT @Return='Y'

RETURN @Return

END;

//来源:C/S框架网(www.csframework.com) QQ:23404761