select * from table where charindex(','+'2'+',',','+value+',') > 0
select * from tab where (','+'2'+',',','+value+',')>0
select * from tablename where charindex('AB',字段1)>0select * from tablename where patindex('%AB%',字段1)>0
create table #t(id int , value varchar(20))insert #t values(1,'2') insert #t values(2,'20,4') insert #t values(3,'30,40,35') insert #t values(4,'2,3,6')select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2' /*--结果 id value ----------- -------------------- 1 2 4 2,3,6(2 row(s) affected) --*/
select * from #t where charindex(',2,',','+value+',') > 0
SQL code select * from #t where charindex(',2,',','+value+',') > 0 还是存在问题呀 如果2在第一个能显示,如2,4,5 但是2在中间就无法显示出来如4,2,5
select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2'这个是对的,独立的2、2在最前、2在最后、2在中间的情况都考虑到了。
create table #t(id int , value varchar(20))insert #t values(1,'2') insert #t values(2,'3,20,4') insert #t values(3,'30,40,35,2') insert #t values(4,'1,2,3,6')select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2' drop table #t ----结果集 (1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected) id value ----------- -------------------- 1 2 3 30,40,35,2 4 1,2,3,6(3 row(s) affected)
select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2' 不对巴。
create table #t(id int , value varchar(20))insert #t values(1,'2') insert #t values(2,'3,20,4') insert #t values(3,'30,40,35,2') insert #t values(4,'1,2,3,6')declare @v varchar(10),@sql varchar(8000) set @v=2set @sql='select * from #t where '',''+[value]+'','' like ''%,'+ltrim(@v)+',%'''Exec(@sql)id value ----------- -------------------- 1 2 3 30,40,35,2 4 1,2,3,6(3 行受影响)
from table
where charindex(','+'2'+',',','+value+',') > 0
create table #t(id int , value varchar(20))insert #t values(1,'2')
insert #t values(2,'20,4')
insert #t values(3,'30,40,35')
insert #t values(4,'2,3,6')select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2'
/*--结果
id value
----------- --------------------
1 2
4 2,3,6(2 row(s) affected)
--*/
select *
from #t
where charindex(',2,',','+value+',') > 0
select *
from #t
where charindex(',2,',','+value+',') > 0 还是存在问题呀
如果2在第一个能显示,如2,4,5
但是2在中间就无法显示出来如4,2,5
insert #t values(2,'3,20,4')
insert #t values(3,'30,40,35,2')
insert #t values(4,'1,2,3,6')select * from #t where [value] like '%,2,%' or value like '2' or value like '2,%' or value like '%,2'
drop table #t
----结果集
(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)(1 row(s) affected)
id value
----------- --------------------
1 2
3 30,40,35,2
4 1,2,3,6(3 row(s) affected)
不对巴。
create table #t(id int , value varchar(20))insert #t values(1,'2')
insert #t values(2,'3,20,4')
insert #t values(3,'30,40,35,2')
insert #t values(4,'1,2,3,6')declare @v varchar(10),@sql varchar(8000)
set @v=2set @sql='select * from #t where '',''+[value]+'','' like ''%,'+ltrim(@v)+',%'''Exec(@sql)id value
----------- --------------------
1 2
3 30,40,35,2
4 1,2,3,6(3 行受影响)