表test id sign
1 1,8,18,8
2 8,28,88
3 11,22,208
4 181,22
5 81,18,88 找出sign中 含有一个8的数据
结果: id sign
1 1,8,18,8
2 8,28,88
1 1,8,18,8
2 8,28,88
3 11,22,208
4 181,22
5 81,18,88 找出sign中 含有一个8的数据
结果: id sign
1 1,8,18,8
2 8,28,88
from tb
where charindex(',8,', ','+sign+',')>0
*
from
test
where
charindex(',8,',','+[sign]+',')>0
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[sign] varchar(9))
insert [test]
select 1,'1,8,18,8' union all
select 2,'8,28,88' union all
select 3,'11,22,208' union all
select 4,'181,22' union all
select 5,'81,18,88'
---查询---
select
*
from
test
where
charindex(',8,',','+[sign]+',')>0---结果---
id sign
----------- ---------
1 1,8,18,8
2 8,28,88(所影响的行数为 2 行)
select
*
from
test
where
','+[sign]+',' like '%,8,%'---结果---
id sign
----------- ---------
1 1,8,18,8
2 8,28,88(所影响的行数为 2 行)
你的格式是怎么样的,如果还是这样的格式是没问题的---测试数据---
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[sign] varchar(9))
insert [test]
select 1,'1,8,18,8' union all
select 2,'8,28,88' union all
select 3,'11,22,208' union all
select 4,'181,22' union all
select 5,'81,18,88' union all
select 6,'3,8,2'
---查询---
select
*
from
test
where
','+[sign]+',' like '%,8,%'---结果---
id sign
----------- ---------
1 1,8,18,8
2 8,28,88
6 3,8,2(所影响的行数为 3 行)
select 2,'8,28,88' union all
select 3,'11,22,208' union all
select 4,'181,22' union all
select 5,'81,18,88' union all
select 6,'3,8'最后数据 3,8 取不到
select *
from (
select id=1,col='1,8,18,8' union all
select 2,'8,28,88' union all
select 3,'11,22,208' union all
select 4,'181,22' union all
select 5,'81,18,88' union all
select 6,'3,8'
) t
where charindex(',8,', ','+col+',')>0
/*
id col
----------- ---------
1 1,8,18,8
2 8,28,88
6 3,8
*/
from (
select id=1,col='1,8,18,8' union all
select 2,'8,28,88' union all
select 3,'11,22,208' union all
select 4,'181,22' union all
select 5,'81,18,88' union all
select 6,'3,8' union all
select 7,'8'
) t
where charindex(',8,', ','+col+',')>0
/*
id col
----------- ---------
1 1,8,18,8
2 8,28,88
6 3,8
7 8
*/
有用replace去掉空格
select
*
from
test
where
charindex(',8,',','+replace([sign],' ','')+',')>0
-->
where charindex(',8,', ','+ltrim(rtrim(col))+',')>0
最后数据 3,8 可以取到