--自定义函数,比较一个字符串中的数字是否在另一个字符串中
create function f_comparestr(@str1 varchar(8000),@str2 varchar(8000))
returns bit
as
begin
declare @tb1 table(st varchar(100))
declare @tb2 table(st varchar(100))
declare @i int,@re bit
select @i=charindex(',',@str1)
while @i>0
begin
insert into @tb1 values(left(@str1,@i-1))
select @str1=substring(@str1,@i+1,8000)
,@i=charindex(',',@str1)
end
if @str1<>'' insert into @tb1 values(@str1)
select @i=charindex(',',@str2)
while @i>0
begin
insert into @tb2 values(left(@str2,@i-1))
select @str2=substring(@str2,@i+1,8000)
,@i=charindex(',',@str2)
end
if @str2<>'' insert into @tb2 values(@str2)
set @re=case when exists(select 1 from @tb1 a join @tb2 b on a.st=b.st)
then 1 else 0 end
return(@re)
end
go
--利用函数实现你的要求
--创建数据测试环境
declare @tb table(id int identity(1,1),[set] varchar(100))
insert into @tb
select '2,3,4,5'
union all select '3,4,7'
union all select '2,8'
union all select '6,3,2'
--查询结果
select * from @tb
where dbo.f_comparestr([set],'2,3')=1
--自定义函数,比较一个字符串中的数字是否在另一个字符串中
create function f_comparestr(@str1 varchar(8000),@str2 varchar(8000))
returns bit
as
begin
declare @tb1 table(st varchar(100))
declare @tb2 table(st varchar(100))
declare @i int,@re bit
select @i=charindex(',',@str1)
while @i>0
begin
insert into @tb1 values(left(@str1,@i-1))
select @str1=substring(@str1,@i+1,8000)
,@i=charindex(',',@str1)
end
if @str1<>'' insert into @tb1 values(@str1)
select @i=charindex(',',@str2)
while @i>0
begin
insert into @tb2 values(left(@str2,@i-1))
select @str2=substring(@str2,@i+1,8000)
,@i=charindex(',',@str2)
end
if @str2<>'' insert into @tb2 values(@str2)
select @re= case when (select sum(1) from @tb2)-
(select sum(1) from @tb2 a inner join @tb1 b on a.st=b.st)=0
then 1 else 0 end
return(@re)
end
go
--利用函数实现你的要求
--创建数据测试环境
declare @tb table(id int identity(1,1),[set] varchar(100))
insert into @tb
select '2,3,4,5'
union all select '3,4,7'
union all select '2,8'
union all select '6,3,2'
--查询结果
select * from @tb
where dbo.f_comparestr([set],'2,3')=1