获取字段中包含某个字符的记录

[btlyeo]

---------------------------------------------------------
declare @tab table (id int IDENTITY (1, 1) NOT NULL ,char1 nvarchar(100))
insert into @tab
select '1035,1036'
union all
select '1035,1,2'
select * from @tab
------------------------------------------------------------------
我想获取@tab表中，包含'1'的数据，是完全匹配的，逗号中间隔的那个数据（意思是只能过滤到第2条记录）

想了半天也没有号办法，用游标的话会很难受，因为数据库里记录太多！

帮忙

[skyzcl]

用函数效率会不会高点

[srgcc]

select * from @tab where char1 ='1' or char1 like '%1,%' or char1 like '%,1,%' or char1 like '%,1'

[wzy_love_sly]

declare @tab table (id int IDENTITY (1, 1) NOT NULL ,char1 nvarchar(100))
insert into @tab
select '1035,1036'
union all
select '1035,1,2'
select * from @tab
where charindex(',1,',','+char1+',')>0

[dawugui]

declare @tab table (id int IDENTITY (1, 1) NOT NULL ,char1 nvarchar(100)) 
insert into @tab 
select '1035,1036' 
union all  
select '1035,1,2' 
select * from @tab where charindex(',1,' , ','+char1 + ',')>0
select * from @tab where ','+char1 + ',' like '%,1,%'

/*
id          char1      
----------- ---------- 
2           1035,1,2

（所影响的行数为 1 行）

id          char1      
----------- ---------- 
2           1035,1,2

（所影响的行数为 1 行）
*/

[pt1314917]

--这样?
declare @tab table (id int IDENTITY (1, 1) NOT NULL ,char1 nvarchar(100)) 
insert into @tab select '1035,1036' 
union all  select '1035,1,2' 


declare @char1 varchar(10)
set @char1='1'
select * from @tab where charindex(','+@char1+',',','+char1+',')>0

[wgzaaa]

select * from @tab WHERE CHARINDEX(',1,',','+CHAR1+',')>0

[btlyeo]

谢谢#23