id c_n
1 2
2 2,2,3,4,5
3 2
select * from ttt where ltrim('2') in (c_n)
这条语句为什么只取出了id为 1,3的， 而没有取出id为2的呢？
id为2的也包含了'2'呀
请指教下哦。谢谢！
------解决方案--------------------------------------------------------
应该是：
select * from ttt where charindex('2',c_n)>0
------解决方案--------------------------------------------------------
你试试看： select '2' in ('2,3,4,5') 返回什么？
------解决方案--------------------------------------------------------
帮顶
------解决方案--------------------------------------------------------
- SQL code
declare @table table (id VARCHAR(2),c_n VARCHAR(14) )insert into @tableselect '1','2' union allselect '2','2,2,3,4,5' union allselect '3','2'select * from @table where CHARINDEX('2',c_n)>0 /*id c_n---- --------------1 22 2,2,3,4,53 2*/select * from @table where c_n LIKE '%2%'/*id c_n---- --------------1 22 2,2,3,4,53 2*/select * from @table where LEN(REPLACE(c_n,'2',''))<LEN(c_n)/*id c_n---- --------------1 22 2,2,3,4,53 2*/--上面的情况都是后面有例如'22,3,4,5,7'也算是有'2'--只取单独为2的：godeclare @table table (id VARCHAR(2),c_n VARCHAR(14) )insert into @tableselect '1','29,3' union allselect '2','1,2,3,4,5' union allselect '3','25,4,22'select * from @table where ','+c_n+',' LIKE '%,2,%'/*id c_n---- --------------2 1,2,3,4,5*/
------解决方案--------------------------------------------------------
------解决方案--------------------------------------------------------
select * from ttt where CHARINDEX('2',c_n)>0
------解决方案--------------------------------------------------------
- SQL code
create table #t(id int not null identity(1,1) primary key,cn varchar(100) not null)/*id c_n1 22 2,2,3,4,53 2*/insert into #tvalues('2'),('2,2,3,4,5'),('2')select * from #twhere CHARINDEX('2',cn,1)>0drop table #t
------解决方案--------------------------------------------------------
你没有理解in的含义.多看看书.