22,206
社区成员
发帖
与我相关
我的任务
分享
create table aaa(id int ,str varchar(1000))
insert aaa values (1,'a,d,c,f')
insert aaa values (2,'f,a')
insert aaa values(3,'aaaa,呵呵,哈哈,123')
insert aaa values(4,'aaaa,呵呵,123')
insert aaa values(5,'哈哈,呵呵,123')
go
--查询包含aaaa 和 哈哈 的。
select * from aaa where ','+str+',' like '%,aaaa,%' or ','+str+',' like '%,哈哈,%'
--id str
--3 aaaa,呵呵,哈哈,123 --包含aaaa和哈哈
--4 aaaa,呵呵,123 --包含aaaa
--5 哈哈,呵呵,123 --包含哈哈
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT,
MEMO VARCHAR(500)
)
INSERT INTO TB
SELECT 1 ,'ad,bc,ds,eg ' UNION ALL
SELECT 2,'ae,dc,ds,fg ' UNION ALL
SELECT 3,'aa,bd,da,eg' UNION ALL
SELECT 4,'ab,bd,ds,fg' UNION ALL
SELECT 5,'ad,bd,da,eg'
DECLARE @STR VARCHAR(500)
SELECT @STR='aa,eg'
DECLARE @SQL VARCHAR(8000)
WHILE @STR LIKE '%,%'
BEGIN
SELECT @SQL=ISNULL(@SQL,'SELECT * FROM TB WHERE 1=0')+ISNULL(' OR MEMO LIKE ''%'+LEFT(@STR,CHARINDEX(',',@STR)-1)+'%''','')
,@STR=STUFF(@STR,1,CHARINDEX(',',@STR),'')
END
EXEC (@SQL+' OR MEMO LIKE ''%'+@STR+'%''')
/*
1 ad,bc,ds,eg
3 aa,bd,da,eg
5 ad,bd,da,eg
*/
create table aaa(id int ,str varchar(1000))
insert aaa values (1,'a,d,c,f')
insert aaa values (2,'f,a')
insert aaa values(3,'aaaa,呵呵,哈哈,123')
insert aaa values(4,'aaaa,呵呵,123')
insert aaa values(5,'哈哈,呵呵,123')
go
select * from aaa where ','+str+',' like '%,aaaa,%' or ','+str+',' like '%,哈哈,%'
--id str
--3 aaaa,呵呵,哈哈,123
--4 aaaa,呵呵,123
--5 哈哈,呵呵,123
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(
ID INT,
MEMO VARCHAR(500)
)
INSERT INTO TB
SELECT 1 ,'A,B,C' UNION ALL
SELECT 2,'C,B,D' UNION ALL
SELECT 3,'C,E,D'
DECLARE @STR VARCHAR(500)
SELECT @STR='B,C'
DECLARE @SQL VARCHAR(8000)
WHILE @STR LIKE '%,%'
BEGIN
SELECT @SQL=ISNULL(@SQL,'SELECT * FROM TB WHERE 1=1')+ISNULL(' AND MEMO LIKE ''%'+LEFT(@STR,CHARINDEX(',',@STR)-1)+'%''','')
,@STR=STUFF(@STR,1,CHARINDEX(',',@STR),'')
END
EXEC (@SQL+' AND MEMO LIKE ''%'+@STR+'%''')
/*
1 A,B,C
2 C,B,D
*/
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(25))
Insert Tab
select 1,N'好,速度,沃尔,撒的' union all
select 2,N'd,e' union all
select 3,N'f'
Go
declare @str1 varchar(20), @str2 varchar(20), @str3 varchar(20)
set @str1='速度,沃尔'
set @str2='沃尔,速度'
set @str3='好,沃尔'
select t1.* from tab t1 ,(
select distinct col1 from(
Select
a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number)
from
Tab a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
substring(','+a.COl2,b.number,1)=','
)t
where charindex(','+col2+',',','+@str1+',')>0 or charindex(','+col2+',',','+@str2+',')>0 or charindex(','+col2+',',','+@str3+',')>0
)t2 where t1.col1=t2.col1
Col1 COl2
----------- -------------------------
1 好,速度,沃尔,撒的
(1 行受影响)
create table aaa(id int ,str varchar(1000))
insert aaa values (1,'a,d,c,f')
insert aaa values (2,'f,a')
insert aaa values(3,'aaaa,呵呵,哈哈,123')
go
select * from aaa where ','+str+',' like '%,aaaa,%' and ','+str+',' like '%,哈哈,%'
---id str
---3 aaaa,呵呵,哈哈,123
--参考
拆分表:
--> --> (Roy)生成測試數據
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab([Col1] int,[COl2] nvarchar(5))
Insert Tab
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
--SQL2000用辅助表:
if object_id('Tempdb..#Num') is not null
drop table #Num
go
select top 100 ID=Identity(int,1,1) into #Num from syscolumns a,syscolumns b
Select
a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID)
from
Tab a,#Num b
where
charindex(',',','+a.Col2,b.ID)=b.ID --也可用 substring(','+a.COl2,b.ID,1)=','
--2000不使用辅助表
Select
a.Col1,COl2=substring(a.Col2,b.number,charindex(',',a.Col2+',',b.number)-b.number)
from
Tab a join master..spt_values b
ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.col2)
where
substring(','+a.COl2,b.number,1)=','
SQL2005用Xml:
select
a.COl1,b.Col2
from
(select Col1,COl2=convert(xml,'<root><v>'+replace(COl2,',','</v><v>')+'</v></root>') from Tab)a
outer apply
(select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v))b
SQL05用CTE:
;with roy as
(select Col1,COl2=cast(left(Col2,charindex(',',Col2+',')-1) as nvarchar(100)),Split=cast(stuff(COl2+',',1,charindex(',',Col2+','),'') as nvarchar(100)) from Tab
union all
select Col1,COl2=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)
select COl1,COl2 from roy order by COl1 option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/