关于字符串拆分，合并问题的整理
[sql]
--关于新方法解决字符串替换和拆分问题的总结
-->TravyLee生成测试数据：[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[ID] int,
[CODE1] varchar(2),
[CODE2] varchar(10)
) www.2cto.com
insert [test]
select 1,'AA','AA BB CC' union all
select 2,'BB','FF EE DD'
with T (id,[CODE1],P1,P2) as
(
select
id,
[CODE1],
charindex(' ',' '+[CODE2]),
charindex(' ',[CODE2]+' ')+1
from
test
union all
select
a.id,
a.CODE1,
b.P2,
charindex(' ',[CODE2]+' ',b.P2)+1
from
test a
join
T b
on
a.id=b.id
where
charindex(' ',[CODE2]+' ',b.P2)>0
)
select www.2cto.com
a.id,
a.CODE1,
name=substring(a.[CODE2]+' ',b.P1,b.P2 - b.P1 - 1)
from
test a
join
T b
on
a.id=b.id
order by
1
/*
id CODE1 name
--------------------------
1 AA AA
1 AA BB
1 AA CC
2 BB FF
2 BB EE
2 BB DD
*/
--> 测试数据：[A1]
if object_id('[A1]') is not null
drop table [A1]
create table [A1](
[编码] varchar(2),
[内容] varchar(1)
)
insert [A1]
select '01','a' union all
select '02','b' union all
select '03','c' union all
select '04','d' union all
select '05','e'
--> 测试数据：[B2]
if object_id('[B2]') is not null
drop table [B2]
create table [B2](
[id] int,
[内容] varchar(11)
)
insert [B2]
select 1,'01,05' union all
select 2,'02' union all
select 3,'01,03' union all
select 4,'02,05' union all
select 5,'01,02,03' union all
select 6,'01,02,04,05' union all
select 7,'02,04'
go www.2cto.com
with t
as(
select
b.id,
a.内容
from
[B2] b
inner join
[A1] a
on
CHARINDEX(a.编码,b.内容)>0
)
select
a.id,
内容=stuff((SELECT ','+内容
from
t
where
a.id=t.id for xml path('')),1,1,'')
from
t a
group by
a.id
/*
id 内容
----------------------
1 a,e
2 b
3 a,c
4 b,e
5 a,b,c
6 a,b,d,e
7 b,d
*/
/*
www.2cto.com
*/
--> --> (Roy)生成
if not object_id('Tab') is null
drop table Tab
Go
Create table Tab(
[Col1] int,
[Col2] nvarchar(1)
)
Insert Tab
select 1,N'a' union all
select 1,N'b' union all
select 1,N'c' union all
select 2,N'd' union all
select 2,N'e' union all
select 3,N'f'
Go
--合并表:
--SQL2000用函数:
go
if object_id('F_Str') is not null
drop function F_Str
go www.2cto.com
create function F_Str(@Col1 int)
returns nvarchar(100)
as
begin
declare @S nvarchar(100)
select @S=isnull(@S+',','')+Col2 from Tab where Col1=@Col1
return @S
end
go
Select distinct Col1,Col2=dbo.F_Str(Col1) from Tab
go
--SQL2005用XML:
--方法1:
select
a.Col1,
Col2=stuff(b.Col2.value('/R[1]','nvarchar(max)'),1,1,'')
from
(select distinct COl1 from Tab) a
Cross apply
(selec