或者:select * from 信件表 where ','+收件人ID+',' like '%,1,%'
SELECT * FROM tb WHERE CHARINDEX(',1,',','+收件人ID+',')>0 ?
declare @t table(id int,发件人ID int,收件人ID nvarchar(20)) insert into @t select 1,4,'1,5,8,9' insert into @t select 2,3,'5,7' insert into @t select 3,6,'1,2,4' insert into @t select 4,8,'9'select * from @t where charindex(',1,' , ','+收件人ID+',')>0 /* id 发件人ID 收件人ID ----------- ----------- -------------------- 1 4 1,5,8,9 3 6 1,2,4 */select * from @t where ','+收件人ID+',' like '%1%' /* id 发件人ID 收件人ID ----------- ----------- -------------------- 1 4 1,5,8,9 3 6 1,2,4 */
---测试数据--- if object_id('[tb]') is not null drop table [tb] go create table [tb]([id] int,[发件人ID] int,[收件人ID] varchar(7)) insert [tb] select 1,4,'1,5,8,9' union all select 2,3,'5,7' union all select 3,6,'1,2,4' union all select 4,8,'9'
---查询--- SELECT * FROM tb WHERE CHARINDEX(',1,',','+收件人ID+',')>0 ---结果--- id 发件人ID 收件人ID ----------- ----------- ------- 1 4 1,5,8,9 3 6 1,2,4(所影响的行数为 2 行)
select * from 表名 where charindex(',1,',','+收件人id+',')>0
declare @id int set @Id=1 select * from 表名 where charindex(','+ltrim(@id)+',',','+收件人id+',')>0
*
FROM
tb
WHERE
CHARINDEX(',1,',','+收件人ID+',')>0
?
declare @t table(id int,发件人ID int,收件人ID nvarchar(20))
insert into @t select 1,4,'1,5,8,9'
insert into @t select 2,3,'5,7'
insert into @t select 3,6,'1,2,4'
insert into @t select 4,8,'9'select * from @t where charindex(',1,' , ','+收件人ID+',')>0
/*
id 发件人ID 收件人ID
----------- ----------- --------------------
1 4 1,5,8,9
3 6 1,2,4
*/select * from @t where ','+收件人ID+',' like '%1%'
/*
id 发件人ID 收件人ID
----------- ----------- --------------------
1 4 1,5,8,9
3 6 1,2,4
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([id] int,[发件人ID] int,[收件人ID] varchar(7))
insert [tb]
select 1,4,'1,5,8,9' union all
select 2,3,'5,7' union all
select 3,6,'1,2,4' union all
select 4,8,'9'
---查询---
SELECT
*
FROM
tb
WHERE
CHARINDEX(',1,',','+收件人ID+',')>0
---结果---
id 发件人ID 收件人ID
----------- ----------- -------
1 4 1,5,8,9
3 6 1,2,4(所影响的行数为 2 行)
set @Id=1
select * from 表名 where charindex(','+ltrim(@id)+',',','+收件人id+',')>0
charindex(',1,',','+收件人ID+',')
'%1%'