Fork me on GitHub
字符串合并与拆分写法小结

字符串合并与拆分写法小结

一. 字符合并

复制代码
if OBJECT_ID('ConcatStr') is not null
drop table ConcatStr
GO
create table ConcatStr
(
ID int,
Code varchar(10)
)
GO
insert into ConcatStr
select 1,'XXX' union all
select 1,'YYY' union all
select 2,'PPP' union all
select 2,'QQQ'
复制代码

 要得到这样的结果:

ID Code
1 XXX,YYY
2 PPP,QQQ

1. 用游标

复制代码
declare @t table(ID int, Code varchar(1000))
declare @id int
declare c cursor for 
select distinct ID from ConcatStr
open c
fetch next from c into @id
while @@fetch_status=0
begin
declare @str varchar(max)
set @str = ''
select @str = @str + ',' + Code from ConcatStr where ID = @id
insert into @t(ID, Code)
select @id,stuff(@str,1,1,'')
fetch next from c into @id
end
close c
deallocate c
select * from @t
复制代码

 2. 用自定义函数

跟游标的方法类似,只是把逐个取的动作封装到函数里去了。
(1) 函数方法1

复制代码
if OBJECT_ID('f_concat_str') is not null
drop function f_concat_str
GO
create function f_concat_str(@id int)
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
set @s=''
select @s = @s+',' + Code from ConcatStr where ID = @id
return (stuff(@s,1,1,''))
--return (right(@s,len(@s)-1)) 
End
复制代码

 (2) 函数方法2,就是把函数1再简化

复制代码
if OBJECT_ID('f_concat_str') is not null
drop function f_concat_str
GO
create function f_concat_str(@id int)
returns nvarchar(4000)
as
begin
declare @s nvarchar(4000)
--set @s=''
--select @s = case when @s = '' then Code else @s + ',' + Code end
--from ConcatStr where ID = @id
select @s = isnull(@s + ',','') + Code from ConcatStr where ID = @id
return @s
end
复制代码

 调用函数1或者函数2

复制代码
--select ID,dbo.f_concat_str(ID) as Code
--from ConcatStr 
--group by ID
Select distinct ID, Code = dbo.f_concat_str(ID) 
from ConcatStr
复制代码

 3. 利用静态的行列转换写法

给分组里的每行构造一个编号,行列转换后把列连接起来,编号多少个,取决于每个分组COUNT(1)的值。

复制代码
SELECT ID,
       MAX(CASE WHEN num = 1 THEN Code ELSE '' END)
     + MAX(CASE WHEN num = 2 THEN ',' + Code ELSE '' END) AS Code
FROM (SELECT ID, Code,
      (SELECT COUNT(*)
         FROM dbo.ConcatStr AS t2
        WHERE t2.ID = t1.ID
          AND t2.Code <= t1.Code) AS num
FROM dbo.ConcatStr AS t1) AS t
GROUP BY ID;
复制代码

 4. 用FOR XML子句

(1) FOR XML AUTO
SQL Server 2000就有这个子句,不过OUTER APPLY是SQL Server 2005的语法。通常这种写法效率上不会比用函数快。

复制代码
SELECT * FROM(SELECT DISTINCT ID FROM ConcatStr)A OUTER APPLY(SELECT Code= STUFF(REPLACE(REPLACE((
SELECT Code FROM ConcatStr N WHERE ID = A.ID FOR XML AUTO), '<N Code="', ','), '"/>', ''), 1, 1, ''))N
复制代码

 (2) FOR XML PATH

SQL Server 2005的新语法。

复制代码
SELECT ID,
STUFF((SELECT ',' + Code
FROM dbo.ConcatStr AS t2
WHERE t2.ID = t1.ID
ORDER BY ID
FOR XML PATH('')), 1, 1, '') AS Code
FROM dbo.ConcatStr AS t1
GROUP BY ID;
复制代码

 

二. 字符拆分

复制代码
if not object_id('SplitStr') is null
drop table SplitStr
Go
create table SplitStr
(
Col1 int,
Col2 nvarchar(10)
)
insert SplitStr
select 1,N'a,b,c' union all
select 2,N'd,e' union all
select 3,N'f'
Go
复制代码

 要得到这样的结果:

Col1 Code
1 a
1 b
1 c
2 d
2 e
3 f

1. 使用数字辅助表

复制代码
if object_id('Tempdb..#Num') is not null
drop table #Num
GO
select top 100 ID = Identity(int,1,1) into #Num 
--也可用ROW_NUMBER()来生成
from syscolumns a,syscolumns b
GO
Select a.Col1,Col2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) 
from SplitStr a,#Num b
where charindex(',',','+a.Col2,b.ID)=b.ID 
--也可用substring(','+a.COl2,b.ID,1)=','
复制代码

 2. 使用CTE

复制代码
with t(Col1, p1, p2)
as
(
select Col1, charindex(',',','+col2), charindex(',',Col2+',') + 1 from SplitStr
union all
select s.Col1, t.p2, charindex(',', s.Col2+',', t.p2) + 1 
from SplitStr s join t on s.Col1 = t.Col1 where charindex(',', s.Col2+',', t.p2) > 0
)
--select * from t
select s.Col1, Col2 = substring(s.Col2+',', t.p1, t.p2-t.p1-1) 
from SplitStr s join t on s.Col1 = t.Col1 
order by s.Col1
option (maxrecursion 0)
复制代码

 3. 使用XML

复制代码
SELECT A.Col1, B.Code
FROM(SELECT Col1, Code = CONVERT(XML,'<root><v>' + REPLACE(Col2, ',', '</v><v>') + '</v></root>') FROM SplitStr) A
OUTER APPLY(SELECT Code = N.v.value('.', 'varchar(100)') FROM A.Code.nodes('/root/v') N(v)) B
复制代码

  

 
 
posted on 2013-08-12 22:50  HackerVirus  阅读(480)  评论(0编辑  收藏  举报