----字符串拆分，合并，替换问题整理贴，有问题的朋友先看看------
--关于新方法解决字符串替换和拆分问题的总结-->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 allselect 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 AA1 AA BB1 AA CC2 BB FF2 BB EE2 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 allselect '02','b' union allselect '03','c' union allselect '04','d' union allselect '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 allselect 2,'02' union allselect 3,'01,03' union allselect 4,'02,05' union allselect 5,'01,02,03' union allselect 6,'01,02,04,05' union allselect 7,'02,04'gowith tas(select b.id, a.内容 from [B2] binner join [A1] aon CHARINDEX(a.编码,b.内容)>0)select a.id, 内容=stuff((SELECT ','+内容 from t where a.id=t.id for xml path('')),1,1,'')from t agroup by a.id/*id 内容----------------------1 a,e2 b3 a,c4 b,e5 a,b,c6 a,b,d,e7 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 TabGoCreate table Tab([Col1] int,[COl2] nvarchar(5))Insert Tabselect 1,N'a,b,c' union allselect 2,N'd,e' union allselect 3,N'f'Go--SQL2000用辅助表:if object_id('Tempdb..#Num') is not null drop table #Numgoselect top 100 ID=Identity(int,1,1) into #Num from syscolumns a, syscolumns bSelect a.Col1,COl2=substring(a.Col2,b.ID,charindex(',',a.Col2+',',b.ID)-b.ID) from Tab a, #Num bwhere 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.Col2from ( select Col1, COl2=convert(xml,'<root><v>' +replace(COl2,',','</v><v>')+'</v></root>') from Tab )aouter apply ( select Col2=C.v.value('.','nvarchar(100)') from a.COl2.nodes('/root/v')C(v) )b/*Col1 COl2----------- -----1 a1 b1 c2 d2 e3 f*/
[解决办法]
好东西啊，学习了
[解决办法]
一句话，CTE查询嘛，你知道CTE查询的限制不？
递归对于超长字符串会有限制，好像是100层吧。其他的都还好，我一般是用循环处理的。
[解决办法]
依然谢谢楼主分享。能加上些分析引导就特别好了！
[解决办法]
[解决办法]
很强！！
[解决办法]
[解决办法]
绝对支持。。。。