----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-02-27 14:40:15
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](col varchar(max))
insert [tb]
SELECT '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
--------------开始查询--------------------------
DECLARE @s VARCHAR(max)
SET @s='2,3'SELECT * FROM TB WHERE CHARINDEX(',2,3,',','+col+',')>0
----------------结果----------------------------
/* col
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2,3,43,65,77,343
2,3,545,6786,12(2 行受影响)
*/
-- Author :fredrickhu(小F,向高手学习)
-- Date :2014-02-27 14:40:15
-- Verstion:
-- Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](col varchar(max))
insert [tb]
SELECT '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
--------------开始查询--------------------------
DECLARE @s VARCHAR(max)
SET @s='2,3'SELECT * FROM TB WHERE CHARINDEX(',2,3,',','+col+',')>0
----------------结果----------------------------
/* col
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2,3,43,65,77,343
2,3,545,6786,12(2 行受影响)
*/
SET @s='2,3'SELECT * FROM TB WHERE CHARINDEX(','+@s+',',','+col+',')>0
select '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
go
select *
from tb
where exists
(
select 1
from
(
select SUBSTRING(t.v, number ,CHARINDEX(',',t.v+',',number)-number) v
from (select '2,3' as v) t,master..spt_values s
where s.number >=1
and s.type = 'P'
and SUBSTRING(','+t.v,s.number,1) = ','
)t
where ','+tb.a+',' like '%,'+t.v+',%'
)
/*
a
1,3,23,4,54,546,67
2,3,43,65,77,343
2,3,545,6786,12
*/
create table wh(x varchar(50))insert into wh(x)
select '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
declare @q varchar(20)
select @q='2,3'select distinct c.x
from wh c
cross join
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'y'
from (select @q 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') d
where charindex(d.y,c.x,1)>0/*
x
--------------------------------------------------
1,3,23,4,54,546,67
2,3,43,65,77,343
2,3,545,6786,12(3 row(s) affected)
*/
create table wh(x varchar(50))insert into wh(x)
select '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
declare @q varchar(20)
select @q='2,12';with t as
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'y'
from (select @q 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',')
select c.x
from wh c
cross join t d
where charindex(','+d.y+',',','+c.x+',',1)>0
group by c.x
having count(1)=(select count(1) from t)/*
x
--------------------------------------------------
2,3,545,6786,12(1 row(s) affected)
*/
insert into wh(x)
select '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
declare @q varchar(20)
select @q='2,3'
select distinct c.x
from wh c
cross join
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'y'
from (select @q 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',') d
where charindex(','+d.y+',',','+c.x+',',1)>0
/*
x
--------------------------------------------------
1,3,23,4,54,546,67
2,3,43,65,77,343
2,3,545,6786,12(3 row(s) affected)
*/
create table wh(x varchar(50))insert into wh(x)
select '1,3,23,4,54,546,67' union all
select '2,3,43,65,77,343' union all
select '2,3,545,6786,12'
declare @q varchar(20)
select @q='2,12';with t as
(select substring(a.s,b.number,charindex(',',a.s+',',b.number)-b.number) 'y'
from (select @q 's') a,master.dbo.spt_values b
where b.type='P' and b.number between 1 and len(a.s)
and substring(','+a.s,b.number,1)=',')
select c.x
from wh c
cross join t d
where charindex(','+d.y+',',','+c.x+',',1)>0
group by c.x
having count(1)=(select count(1) from t)/*
x
--------------------------------------------------
2,3,545,6786,12(1 row(s) affected)
*/
说实话 有点看不懂,,有时间一定要看看 3Q 哈,先用了!