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

字符串合并与拆分写法小结一. 字符合并?1234567891011121314if OBJECT_ID(ConcatStr) is not nulldrop table ConcatStrGOcreate table ConcatStr(ID int,Code varchar(10)GOinsert into ConcatStrselect 1,XXX union allselect 1,YYY union allselect 2,PPP union allselect 2,QQQ要得到这样的结果：?123ID Code1 XXX,YYY2 PPP,QQQ1. 用游标?123456789101112131415161718declare t table(ID int, Code varchar(1000)declare id intdeclare c cursor forselect distinct ID from ConcatStropen cfetch next from c into idwhile fetch_status=0begindeclare str varchar(max)set str = select str = str + , + Code from ConcatStr where ID = idinsert into t(ID, Code)select id,stuff(str,1,1,)fetch next from c into idendclose cdeallocate cselect * from t2. 用自定义函数跟游标的方法类似，只是把逐个取的动作封装到函数里去了。(1) 函数方法1?12345678910111213if OBJECT_ID(f_concat_str) is not nulldrop function f_concat_strGOcreate function f_concat_str(id int)returns nvarchar(4000)asbegindeclare s nvarchar(4000)set s=select s = s+, + Code from ConcatStr where ID = idreturn (stuff(s,1,1,)-return (right(s,len(s)-1) End(2) 函数方法2，就是把函数1再简化?1234567891011121314if OBJECT_ID(f_concat_str) is not nulldrop function f_concat_strGOcreate function f_concat_str(id int)returns nvarchar(4000)asbegindeclare s nvarchar(4000)-set s=-select s = case when s = then Code else s + , + Code end-from ConcatStr where ID = idselect s = isnull(s + ,) + Code from ConcatStr where ID = idreturn send调用函数1或者函数2?12345-select ID,dbo.f_concat_str(ID) as Code-from ConcatStr -group by IDSelect distinct ID, Code = dbo.f_concat_str(ID) from ConcatStr3. 利用静态的行列转换写法给分组里的每行构造一个编号，行列转换后把列连接起来，编号多少个，取决于每个分组COUNT(1)的值。?12345678910SELECT ID,MAX(CASE WHEN num = 1 THEN Code ELSE END)+ MAX(CASE WHEN num = 2 THEN , + Code ELSE END) AS CodeFROM (SELECT ID, Code,(SELECT COUNT(*)FROM dbo.ConcatStr AS t2WHERE t2.ID = t1.IDAND t2.Code = t1.Code) AS numFROM dbo.ConcatStr AS t1) AS tGROUP BY ID;4. 用FOR XML子句(1) FOR XML AUTOSQL Server 2000就有这个子句，不过OUTER APPLY是SQL Server 2005的语法。通常这种写法效率上不会比用函数快。?12SELECT * 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), , ), 1, 1, )N(2) FOR XML PATHSQL Server 2005的新语法。?12345678SELECT ID,STUFF(SELECT , + CodeFROM dbo.ConcatStr AS t2WHERE t2.ID = t1.IDORDER BY IDFOR XML PATH(), 1, 1, ) AS CodeFROM dbo.ConcatStr AS t1GROUP BY ID;二. 字符拆分?12345678910111213if not object_id(SplitStr) is nulldrop table SplitStrGocreate table SplitStr(Col1 int,Col2 nvarchar(10)insert SplitStrselect 1,Na,b,c union allselect 2,Nd,e union allselect 3,NfGo要得到这样的结果：?1234567Col1 Code1 a1 b1 c2 d2 e3 f?12345678910111213141516171819202122232425262728293031323334353637381. 使用数字辅助表if object_id(Tempdb.#Num) is not nulldrop table #NumGOselect top 100 ID = Identity(int,1,1) into #Num -也可用ROW_NUMBER()来生成from syscolumns a,syscolumns bGOSelect a.Col1,Col2=substring(a.Col2,b.ID,charindex(,a.Col2+,b.ID)-b.ID) from SplitStr a,#Num bwhere charindex(,+a.Col2,b.ID)=b.ID -也可用substring(,+a.COl2,b.ID,1)=,2. 使用CTEwith t(Col1, p1, p2)as(select Col1, charindex(,+col2), charindex(,Col2+,) + 1 from SplitStrunion allselect 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 tselect 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.Col1option (maxrecursion 0)3. 使用XMLSELECT A