写个函数来判断:
CREATE FUNCTION dbo.f_1(@str varchar(8000),@c varchar(10),@st varchar(8000))
RETURNS bit
AS
BEGIN
DECLARE @s varchar(8000),@ret bit
SET @ret=1
SET @s=@str
WHILE CHARINDEX(@c,@s)>0
BEGIN
if @c+@st+@c not like '%'+@c+LEFT(@s,CHARINDEX(@c,@s)-1)+@c+'%'
BEGIN
SET @ret=0
BREAK
END
SET @s=RIGHT(@s,LEN(@s)-CHARINDEX(@c,@s))
END
if @c+@st+@c not like '%'+@c+@s+@c+'%'
SET @ret=0
RETURN(@ret)
END
go
--调用
declare @b varchar(8000)
set @b='3,6'
select dbo.f_1(@b,',',a) from t
set nocount on
declare @s varchar(8000),@sql nvarchar(4000)
set @s='3,6'
set @sql='select '''+replace(@s,',',''' col union all select ''')+''' col'
set @sql='select * into ##tem from ('+@sql+') t'
exec sp_executesql @sql
if exists(select 1 from 表 where a in(select col from ##tem))
print 'true'
else
print 'false'
drop table ##tem
set nocount off
--测试四,另一种的求含有3,6或只含3或6的办法
declare @b varchar(100)
declare @c varchar(10)
declare @d varchar(10)
set @b='3,6'
set @c=replace(@b,',','')
set @d=replace(@d,',','%')
select a,case when a like '%['+@c+']%' or a like '%'+@d+'%' then 'true' else 'false' end from tb
--测试三,含有(3,6)中任意一个
declare @b varchar(100)
declare @c varchar(10)
declare @d varchar(10)
set @b='3,6'
set @c=replace(@b,',','')
select a,case when a like '%['+@c+']%' then 'true' else 'false' end from tb
--结果
1,2,3,4,5,6 true
2,3,4 true
4,5,6 true
7,8,9 false
--建表
create table tb(a varchar(100))
--插入数据
insert into tb select '1,2,3,4,5,6'
union select
'2,3,4'
union select
'4,5,6'
union select
'7,8,9'
--测试一
declare @b varchar(100)
declare @c varchar(10)
declare @d varchar(10)
set @b='3,6'
set @c=left(@b,1)
set @d=right(@b,1)
select a,case when a like '%'+@c+'%'+@d+'%' or a like '%'+@c+'%' or a like '%'+@d+'%'then 'true' else 'false' end from tb
declare @b varchar(100)
declare @c varchar(10)
set @b='3,6'
set @c=replace(@b,',','%')
select a,case when a like '%'+@c+'%' then 'true' else 'false' end from tb
--结果:
1,2,3,4,5,6 true
2,3,4 false
4,5,6 false
7,8,9 false