两个字符串,以特定符号分隔(例如‘,’号),求交集
第一种情况:
declare @m varchar(100),@n varchar(100)
select
@m=',2,3,5,7,8,9,10,',
@n=',1,3,6,8,10,'
select
--count(1)
result=substring(@m,number,charindex(',',@m,number)-number)
from master..spt_values
where number<len(@m) and type='p' and number>1
and substring(','+@m,number,1)=','
and charindex(substring(@m,number-1,charindex(',',@m,number)-number+2),@n)>0
第二种情况:
declare @m varchar(100),@n varchar(100)
select
@m='2,3,5,7,8,9,10',
@n='1,3,6,8,10'
select
--COUNT(1)
result=substring(@m,number,charindex(',',@m+',',number)-number)
from master.dbo.spt_values
where type='p' and number>1 and number<len(@m)
and substring(','+@m,number,1)=','
and charindex(substring(@m,number-1,charindex(',',@m+',',number)-number + 2),','+@n)>0
以上两种情况,如果把“result=”那一行的sql语句换成count(1),则可以用来判断两个字符串是否有交集,count的结果值大于0就是有交集(count的数值就是交集的字符个数)
如果要把所有交集的字符都输出,那么就用“result=”那一行就可以了。
https://www.cnblogs.com/tlduck/p/8118314.html
另一个人的做法:
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
转载至https://blog.csdn.net/chadcao/article/details/133763453