--创建用户定义函数
create function f_StrCmp(
@strA varchar(1000), --源字符串
@strB varchar(1000), --目的字符串
@separator varchar(10)) --分隔符
returns int --1:匹配一致;else:匹配不一致
as
begin
while charindex(@separator,@strB)>0
begin
if charindex(@separator+left(@strB,charindex(@separator,@strB)-1)+@separator,@separator+@strA+@separator)=0
return 0
set @strB=stuff(@strB,1,charindex(@separator,@strB)+len(@separator)-1,'')
end
return 1
end
go
--调用示例:
select dbo.f_StrCmp('AAAA,BBBB,CCCC','CCCC,BBBB,AAAA',',')
select dbo.f_StrCmp('AAAA,BBB,CCCC','CCCC,BBBB,AAAA',',')
go
--楼主的SQL可修改为:select * from a where dbo.f_StrCmp(f1,@var,',')=1
select jobno from pfm..spylog group by jobno order by max(casetype)
create function kk(@val1 varchar(1000),@val2 varchar(1000))
returns varchar(10)
as
declare @value varchar(20)
set @val2=@val2+';'
while len(@val2)>1
begin
set @value=substring(@val2,1,charindex(';',@val2))
if charindex(@value,@val1)=0
return false
set @val2 = right(@val2,len(@val2)-charindex(';',@val2)-1)
end
return true
go