sql server判断字符串A101;A102和字符串A102;A103是否存在交集

我们编写两个函数：

1）函数fn_split将字符串拆分成集合

create function [dbo].[fn_split](@inputstr varchar(8000), @seprator varchar(10))
returns @temp table (Result varchar(200))
as 
begindeclare @i intset @inputstr = rtrim(ltrim(@inputstr))set @i = charindex(@seprator , @inputstr)while @i >= 1begininsert @temp values(left(@inputstr , @i - 1))set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)set @i = charindex(@seprator , @inputstr)endif @inputstr <> '\'insert @temp values(@inputstr)return 
end

2）函数fn_intersection判断两个集合是否有交集（两个字符串是否有交集）

create FUNCTION [dbo].[fn_intersection](@str1 VARCHAR(8000),@str2 VARCHAR(8000), @seprator VARCHAR(10))
RETURNS INT
AS 
BEGINDECLARE @count INTSELECT @count=COUNT(1) FROM dbo.fn_split(@str1,@seprator) aINNER JOIN dbo.fn_split(@str2,@seprator) b ON a.Result=b.ResultRETURN (@count)
END

3）字符串测试：

--没有交集返回0
SELECT dbo.fn_intersection('A101;A102','A1;A103',';')
--有交集返回1
SELECT dbo.fn_intersection('A101;A102','A102;A103',';')

4）表测试：

select * from DES_SupplierBasicInfomation 
WHERE dbo.fn_intersection(ProductTypeCodeList,'D.25;D.26',';')>0