问一句简单SQL语句 查询一个包含字符串

Question

比如一个字符串有  1111,2222,3333,1112,1113,1114查询 含有 1111的字符串 但是 111不能出现三次。 一句SQL查询 该如何写呢？ 请教高人！

Answer 1

CHARINDEX(','+'11111'+',',','+@S+',')>0

Answer 2

select *
from tb
where len(col)-len(replace(col,'111',''))<>3

Answer 3

--如果有三个111被replace为空，那么不存在len(replace(@sql,'111',''))>=len(@sql)-6
--替换一个或两111被replace为空，那么存在len(replace(@sql,'111',''))>=len(@sql)-6declare @sql varchar(1000)
set @sql='1111,2222,3333,1112,1113,1114'select @sql where charindex('1111',@sql)>0 and len(replace(@sql,'111',''))>len(@sql)-6
set @sql='1111,2222,3333,1112'
select @sql where charindex('1111',@sql)>0 and len(replace(@sql,'111',''))>=len(@sql)-6----------------------------（所影响的行数为 0 行）                                 
----------------------------
1111,2222,3333,1112（所影响的行数为 1 行）

Answer 4

select *
from tb
where len(col)-len(replace(col,'111',''))<>3
and CHARINDEX(','+'1111'+',',','+ col +',')>0

Answer 5

select
 *
from
 tb
where
 len(col)-len(replace(col,'111',''))<3
and
 charindex(','+'11111'+',',','+col+',')>0