求一sql 算法 欢迎进来跟帖 跟帖也加分

Question

create table #tba(name nvarchar(20), idList nvarchar(300))
 insert into #tba(name,idList) values('韩冰','-1')
 insert into #tba(name,idList) values('烈火','-1,10')
 insert into #tba(name,idList) values('思孤寒','-1,9,7,8')
 insert into #tba(name,idList) values('小猫','-1,99,7,9')
 insert into #tba(name,idList) values('皮皮','-1,99')
 select * from #tba数据表结构介绍：
Name 名称列 
idList 该列为Id的集合列，其中Id以逗号分割
功能要求
我要提取Id集合列中不包含id=9的数据，但是不能把类似id=99或id=999的数据过滤掉

Answer 1

select * from #tba
where idList like '9,%'
or idList like '%,9,%'
or idList like '%,9';

Answer 2

create table #tba(name nvarchar(20), idList nvarchar(300))
 insert into #tba(name,idList) values('韩冰','-1')
 insert into #tba(name,idList) values('烈火','-1,10')
 insert into #tba(name,idList) values('思孤寒','-1,9,7,8')
 insert into #tba(name,idList) values('小猫','-1,99,7,9')
 insert into #tba(name,idList) values('皮皮','-1,99')
 
 select * from #tba where CHARINDEX(',9,',','+idList+',')=0/*
name                 idList
-------------------- ----------
韩冰                   -1
烈火                   -1,10
皮皮                   -1,99(3 行受影响)                   

Answer 3

select * from #tba
where charindex(',9,',','+idList+',')>0

Answer 4

create table #tba(name nvarchar(20), idList nvarchar(300))
 insert into #tba(name,idList) values('韩冰','-1')
 insert into #tba(name,idList) values('烈火','-1,10')
 insert into #tba(name,idList) values('思孤寒','-1,9,7,8')
 insert into #tba(name,idList) values('小猫','-1,99,7,9')
 insert into #tba(name,idList) values('皮皮','-1,99')
 select * from #tba where ','+idList+',' not like '%,9,%'
/*
name                 idList
-------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
韩冰                   -1
烈火                   -1,10
皮皮                   -1,99(3 行受影响)*/