select * from tableA where charindex(','+Rtrim(id)+',',',1,2,100,')>0
create table tablea(id int) insert into tablea select 1 insert into tablea select 2 insert into tablea select 3 insert into tablea select 4 insert into tablea select 5declare @s varchar(1000),@s1 varchar(1000) set @s1='' set @s='1,2,100,5,4,66,3,88,99' set @s=@s+',' while charindex(',',@s)>0 begin if exists(select * from tablea where [id]=convert(int,left(@s,charindex(',',@s)-1))) set @s=stuff(@s,1,charindex(',',@s),'') else begin set @s1=@s1+','+left(@s,charindex(',',@s)-1) set @s=stuff(@s,1,charindex(',',@s),'') end end set @s1=stuff(@s1,1,1,'') print @s1/*The result:*/100,66,88,99
select usercode from users -- 传来一个字符串,查出字符串里usercode的记录 declare @strIn varchar(8000) declare @strNotIn varchar(1000) select @strIn = @strIn +',' + usercode from table where charindex(','+Rtrim(id)+',',',1,2,100,')>0 set @strIn = stuff(@strIn,1,1,'') set @strNotIn = replace('1,2,100',@strIn ,'') print @strNotIn 即可求出100
declare @strIn varchar(8000) declare @strNotIn varchar(1000) select @strIn = @strIn +',' + usercode from table where charindex(','+Rtrim(id)+',',',1,2,100,')>0 set @strIn = stuff(@strIn,1,1,'') set @strNotIn = replace('1,2,100',@strIn ,'') print @strNotIn 即可求出100
insert into tablea select 1
insert into tablea select 2
insert into tablea select 3
insert into tablea select 4
insert into tablea select 5declare @s varchar(1000),@s1 varchar(1000)
set @s1=''
set @s='1,2,100,5,4,66,3,88,99'
set @s=@s+','
while charindex(',',@s)>0
begin
if exists(select * from tablea where [id]=convert(int,left(@s,charindex(',',@s)-1)))
set @s=stuff(@s,1,charindex(',',@s),'')
else
begin
set @s1=@s1+','+left(@s,charindex(',',@s)-1)
set @s=stuff(@s,1,charindex(',',@s),'')
end
end
set @s1=stuff(@s1,1,1,'')
print @s1/*The result:*/100,66,88,99
-- 传来一个字符串,查出字符串里usercode的记录
declare @strIn varchar(8000)
declare @strNotIn varchar(1000)
select @strIn = @strIn +',' + usercode from table where charindex(','+Rtrim(id)+',',',1,2,100,')>0
set @strIn = stuff(@strIn,1,1,'')
set @strNotIn = replace('1,2,100',@strIn ,'')
print @strNotIn
即可求出100
declare @strNotIn varchar(1000)
select @strIn = @strIn +',' + usercode from table where charindex(','+Rtrim(id)+',',',1,2,100,')>0
set @strIn = stuff(@strIn,1,1,'')
set @strNotIn = replace('1,2,100',@strIn ,'')
print @strNotIn
即可求出100