declare @t table(field int)
insert @t
select 1 union all
select 2 union all
select 3declare @id varchar(20)
set @id = '2,3,4'
select * from @t where charindex(',' + cast(field as varchar) + ',',',' + @id + ',') > 0/*结果
field
-----------
2
3
*/
insert @t
select 1 union all
select 2 union all
select 3declare @id varchar(20)
set @id = '2,3,4'
select * from @t where charindex(',' + cast(field as varchar) + ',',',' + @id + ',') > 0/*结果
field
-----------
2
3
*/
where charindex(',2,',','+field+',')>0 or charindex(',3,',','+field+',')>0 or charindex(',4,',','+field+',')>0
select * from a where field in (select id from b)
insert into a select '1,2,3'
insert into a select '4,5,6'
insert into a select '5,6,7'
insert into a select '6,7,8'
insert into a select '3,6,9'
gocreate function f_str(@str1 varchar(20),@str2 varchar(20))
returns int
as
begin
declare @t table(str varchar(20))
set @str2=@str2+','
while charindex(',',@str2)>0
begin
insert into @t select left(@str2,charindex(',',@str2)-1)
set @str2=stuff(@str2,1,charindex(',',@str2),'')
end
if exists(select 1 from @t where isnull(str,'')<>'' and charindex(','+str+',',','+@str1+',')>0)
return 1
return 0
end
goselect * from a where dbo.f_str(field,'2,3,4')=1/*
field
--------------------
1,2,3
4,5,6
3,6,9
*/
godrop function f_str
drop table a
go
不过不太会用函数,子陌的答案应该很接近
我也正在查资料。这题的意思就是查出一个字符串中指定表达式中某模式的内容
2,3,4 中2和3都在1,2,3中出现,只要有一个相同就应该查出该字段
select * from a where charindex(cast(@p as varchar(10)),a.field,1)>0