--关于新方法解决字符串替换和拆分问题的总结
-->TravyLee生成测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[ID] int,
[CODE1] varchar(2),
[CODE2] varchar(10)
)
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
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
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
*/
/*
整理人:中国风(Roy)
日期:2008.06.06
*/
--> --> (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
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
(select
COl2=(select N','+Col2 from Tab where Col1=a.COl1
For XML PATH(''), ROOT('R'), TYPE))b
--方法2:
select
a.Col1,
COl2=replace(b.Col2.value('/Tab[1]','nvarchar(max)'),char(44)+char(32),char(44))
from
(select distinct COl1 from Tab) a
cross apply
(
select
Col2=(select COl2 from Tab where COl1=a.COl1 FOR XML AUTO, TYPE)
.query(' <Tab>
{for $i in /Tab[position() <last()]/@COl2 return concat(string($i),",")}
{concat("",string(/Tab[last()]/@COl2))}
</Tab>')
)b
--SQL05用CTE:
;with roy as(
select
Col1,
Col2,
row=row_number()over(partition by COl1 order by COl1)
from
Tab
)
,Roy2 as
(
select
COl1,
cast(COl2 as nvarchar(100))COl2,row
from
Roy
where
row=1
union all
select
a.Col1,
cast(b.COl2+','+a.COl2 as nvarchar(100)),a.row
from
Roy a
join
Roy2 b
on
a.COl1=b.COl1 and a.row=b.row+1
)
select
Col1,
Col2
from
Roy2 a
where
row=(
select
max(row)
from
roy
where Col1=a.COl1
)
order by
Col1
option (MAXRECURSION 0)
生成结果:
/*
Col1 COl2
----------- ------------
1 a,b,c
2 d,e
3 f
(3 行受影响)
*/
--> --> (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)=','
--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
/*
Col1 COl2
----------- -----
1 a
1 b
1 c
2 d
2 e
3 f
*/
关于字符串拆分,合并问题的整理
最新推荐文章于 2022-06-20 14:06:18 发布