有如下数据表

需求就是将Col1，Col2按照特定的字符串分割成多行

一、利用XML解析方式

      先将该字段值统一替换为逗号分割，再将逗号分割替换转为XML数据类型，再利用xml转为多个行

declare @table1 table ( ID int , Col1 nvarchar(50) , Col2 nvarchar(50) );insert into @table1 values ( 1, ‘a,b,c‘, ‘诶，必，塞，地，伊‘ );insert into @table1 values ( 2, ‘w‘, N‘三四，不知道咧‘ );--方式一select a.ID, a.Col1, a.Col2, v1, v2from ( select ID, Col1, Col2, convert(xml, ‘<n>‘ + replace(replace(Col1, ‘，‘, ‘,‘), ‘,‘, ‘</n><n>‘) + ‘</n>‘) as xmlval1 , convert(xml, ‘<n>‘ + replace(replace(Col2, ‘，‘, ‘,‘), ‘,‘, ‘</n><n>‘) + ‘</n>‘) as xmlval2 from @table1 ) a cross apply ( select k.n.value(‘.‘, ‘nvarchar(80)‘) v1 from a.xmlval1.nodes(‘n‘) k(n) ) bs cross apply ( select k.n.value(‘.‘, ‘nvarchar(80)‘) v2 from a.xmlval2.nodes(‘n‘) k(n) ) ns;--方式二select ID, t.Col1,t.Col2, v1, v2from @table1 as t cross apply ( values (convert(xml, ‘<n>‘ + replace(replace(Col1, ‘，‘, ‘,‘), ‘,‘, ‘</n><n>‘)+ ‘</n>‘), convert(xml, ‘<n>‘ + replace(replace(Col2, ‘，‘, ‘,‘), ‘,‘, ‘</n><n>‘)+ ‘</n>‘)) ) a (xmlval1 , xmlval2 ) cross apply ( select k.n.value(‘.‘, ‘varchar(80)‘) as v1 from a.xmlval1.nodes(‘n‘) k(n)) bs cross apply ( select k.n.value(‘.‘, ‘varchar(80)‘) as v2 from a.xmlval2.nodes(‘n‘) k(n) ) ns;

二、利用通用表达式CTE

;with CTEas ( select *, row_number() over ( partition by id order by ( select 1 )) as SEQ from @table1 )select A.ID, substring(Col1, B.number, charindex(‘,‘, Col1 + ‘,‘, B.number) - B.number) as single_agefrom CTE A inner join master..spt_values B on charindex(‘,‘, ‘,‘ + Col1, B.number) = B.numberwhere B.type = ‘P‘order by id, SEQ, B.number;

三、创建自定义函数

--1. 创建fn_Split函数. ( 切分字符串， 返回一个列名为id的表 ) IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(‘fn_Split‘) AND (TYPE = ‘FN‘ OR TYPE = ‘TF‘ OR TYPE = ‘IF‘) ) DROP FUNCTION fn_Split GO CREATE FUNCTION [dbo].[fn_Split]( @str VARCHAR(MAX), @separator VARCHAR(10))RETURNS TABLEAS RETURN ( --Example: SELECT id FROM fn_Split(‘a,b,d,c‘,‘,‘)  SELECT B.id FROM ( ( --A　的作用只是生成 ‘<v>a</v><v>b</v><v>d</v><v>c</v>‘ 的XML格式的数据, 提供数据源  SELECT [value] = CONVERT(XML, ‘<v>‘ + REPLACE(@str, @separator, ‘</v><v>‘) + ‘</v>‘) ) A OUTER APPLY ( --B 的作用是将A中的 XML 数据的值枚举出来转换成行 SELECT id = N.v.value(‘.‘, ‘varchar(100)‘) FROM A.[value].nodes(‘/v‘) N(v) ) B ) )GO

使用函数

DECLARE @t TABLE (id INT,age NVARCHAR(MAX))INSERT INTO @t VALUES(1,‘23|24|25|26|29‘)INSERT INTO @t VALUES(1,‘33|aa|bb|cc|ss‘)INSERT INTO @t VALUES(2,‘35|BB|CC|YY|RR‘) SELECT a.id,b.id AS item FROM @t a CROSS APPLY dbo.fn_Split(a.age,‘|‘) AS b