sql server判断两个集合字符串是否存在交集

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 
begin
  declare @i int
  set @inputstr = rtrim(ltrim(@inputstr))
  set @i = charindex(@seprator , @inputstr)
  while @i >= 1
  begin
    insert @temp values(left(@inputstr , @i - 1))
    set @inputstr = substring(@inputstr , @i + 1 , len(@inputstr) - @i)
    set @i = charindex(@seprator , @inputstr)
  end
  if @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 
BEGIN
	DECLARE @count INT
	SELECT @count=COUNT(1) FROM dbo.fn_split(@str1,@seprator) a
	INNER JOIN dbo.fn_split(@str2,@seprator) b ON a.Result=b.Result
	RETURN (@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