用最简单的方法比较两个字符串. 判断第一个字符串是否被包含在第二个中.
如果被包含则为1 .charindex不行.
例如:('1') 和 (10,12) 结果 0
('1') 和 (10,1) 结果 1
('5') 和 ('15','25','10') 结果为 0
('5') 和 ('15','25','5') 结果为 1
如果被包含则为1 .charindex不行.
例如:('1') 和 (10,12) 结果 0
('1') 和 (10,1) 结果 1
('5') 和 ('15','25','10') 结果为 0
('5') 和 ('15','25','5') 结果为 1
('1') 和 (10,1) 结果 1
??
charindex (',1,' , ',' + (10,12) + ',')
insert into tb values('1','10,12')
insert into tb values('1','10,1')
insert into tb values('5','15,25,10')
insert into tb values('5','15,25,5')
goselect c1 , c2 , 结果 = (len(c2) - len(replace(','+c2+',' , ','+c1+',' , '')) + 2) / (len(c1) + 1) from tb where charindex(','+c1+',' , ','+c2+',') > 0
union all
select c1 , c2 , 0 from tb where charindex(','+c1+',' , ','+c2+',') = 0drop table tb/*
c1 c2 结果
---------- -------------------- -----------
1 10,1 1
5 15,25,5 1
1 10,12 0
5 15,25,10 0(所影响的行数为 4 行)
*/
create table tb(c1 varchar(10), c2 varchar(20))
insert into tb values('1','10,12')
insert into tb values('1','10,1')
insert into tb values('5','15,25,10')
insert into tb values('5','15,25,5')
insert into tb values('2','15,,2,25,5')
goselect c1 , c2 , 结果 = (len(c2) - len(replace(','+c2+',' , ','+c1+',' , '')) + 2) / (len(c1) + 1) from tb where charindex(','+c1+',' , ','+c2+',') > 0
union all
select c1 , c2 , 0 from tb where charindex(','+c1+',' , ','+c2+',') = 0drop table tb/*
c1 c2 结果
---------- -------------------- -----------
1 10,1 1
5 15,25,5 1
2 15,,2,25,5 1
1 10,12 0
5 15,25,10 0(所影响的行数为 5 行)
*/--如果是只是查是否在其中,则如下:create table tb(c1 varchar(10), c2 varchar(20))
insert into tb values('1','10,12')
insert into tb values('1','10,1')
insert into tb values('5','15,25,10')
insert into tb values('5','15,25,5')
insert into tb values('2','15,,2,25,5')
goselect c1 , c2 , 结果 = 1 from tb where charindex(','+c1+',' , ','+c2+',') > 0
union all
select c1 , c2 , 0 from tb where charindex(','+c1+',' , ','+c2+',') = 0drop table tb/*
c1 c2 结果
---------- -------------------- -----------
1 10,1 1
5 15,25,5 1
2 15,,2,25,5 1
1 10,12 0
5 15,25,10 0(所影响的行数为 5 行)
*/