select * from 表A where A like ''%3%' or A like '%56%' or A like '%600%'
declare @s varchar(8000),@len int set @s='3,56,600' set @len= len(@s)+1 set rowcount @len select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,( select col=substring(a,id,charindex(',',a+',',id)-id) from # where id<=len(a+'a') and charindex(',',','+a,id)=id ) b where charindex(b.col,a.a)>0 这样应该就可以了
declare @s varchar(8000),@len int set @s='3,56,600' set @len= len(@s)+1 set rowcount @len select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,( select col=substring(@s,id,charindex(',',@s+',',id)-id) from # where id<=len(@s+'a') and charindex(',',','+@s,id)=id ) b where charindex(b.col,a.a)>0 这样应该就可以了
declare @s varchar(8000),@len int set @s='3,56,600' set @len= len(@s)+1 set rowcount @len select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,( select col=substring(@s,id,charindex(',',@s+',',id)-id) from # where id<=len(@s+'a') and charindex(',',','+@s,id)=id ) b where charindex(b.col,a.a)>0 这样应该就可以了看不懂也!
declare @s varchar(8000),@v varchar(100) set @v = '3,56,600' select @s = 'charindex('','+replace(@v,',',','','',''+col+'','')>0 or charindex('',')+','','',''+col+'','')>0' set @s = 'select * from A where '+@s
create table A(col varchar(1000)) insert into A select rtrim('1 ') insert into A select rtrim('1,3 ') insert into A select rtrim('1,56,2000 ') insert into A select rtrim('3,5500,600')declare @s varchar(8000),@v varchar(100) set @v = '3,56,600' select @s = 'charindex('','+replace(@v,',',','','',''+col+'','')>0 or charindex('',')+','','',''+col+'','')>0' set @s = 'select * from A where '+@s exec(@s)/* col ------------ 1,3 1,56,2000 3,5500,600 */drop table A
where A like ''%3%' or A like '%56%' or A like '%600%'
set @s='3,56,600'
set @len= len(@s)+1
set rowcount @len
select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,(
select col=substring(a,id,charindex(',',a+',',id)-id)
from # where id<=len(a+'a')
and charindex(',',','+a,id)=id ) b
where charindex(b.col,a.a)>0
这样应该就可以了
set @s='3,56,600'
set @len= len(@s)+1
set rowcount @len
select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,(
select col=substring(@s,id,charindex(',',@s+',',id)-id)
from # where id<=len(@s+'a')
and charindex(',',','+@s,id)=id ) b
where charindex(b.col,a.a)>0
这样应该就可以了
set @s='3,56,600'
set @len= len(@s)+1
set rowcount @len
select id=identity(int,1,1) into # from syscolums,sysobjectsselect distinct a from a ,(
select col=substring(@s,id,charindex(',',@s+',',id)-id)
from # where id<=len(@s+'a')
and charindex(',',','+@s,id)=id ) b
where charindex(b.col,a.a)>0
这样应该就可以了看不懂也!
set @v = '3,56,600'
select @s = 'charindex('','+replace(@v,',',','','',''+col+'','')>0 or charindex('',')+','','',''+col+'','')>0'
set @s = 'select * from A where '+@s
insert into A select rtrim('1 ')
insert into A select rtrim('1,3 ')
insert into A select rtrim('1,56,2000 ')
insert into A select rtrim('3,5500,600')declare @s varchar(8000),@v varchar(100)
set @v = '3,56,600'
select @s = 'charindex('','+replace(@v,',',','','',''+col+'','')>0 or charindex('',')+','','',''+col+'','')>0'
set @s = 'select * from A where '+@s
exec(@s)/*
col
------------
1,3
1,56,2000
3,5500,600
*/drop table A