mysql将冒号分隔的串分成多条记录_SQL将一个字段内用逗号分隔的内容分成多条记录...

由于业务需求，我们可能会把一串以分割符字符串数据放到一个字段，如我们在客户端处理拆分是很简单的，不过这样做效果不太好，怎么用SQL SERVER 2008 来解决这件事件哪？

方案1

用SQL SERVER XML 功能来解决

1 ----> 生成测试数据: @T

2  DECLARE @T TABLE (id INT,Col1 VARCHAR(50),Col2 VARCHAR(50))

3  INSERT INTO @T

4  SELECT 1,'卸货费','运输车队业务, 客服接单业务'

5  UNION ALL

6  SELECT 2,'报关费','报关业务、客服接单业务'

----SQL查询如下:

SELECT

T.id,

A.x.value('.','varchar(10)') AS code,

B.x.value('.','varchar(10)') AS code2

FROM (

SELECT *,doc = CONVERT(xml,''+REPLACE(code,',','')+''),

doc2=CONVERT(xml,''+REPLACE(code2,',','')+'')

FROM @T

) AS T

CROSS APPLY doc.nodes('//v') AS A(x)

CROSS APPLY doc2.nodes('//v') AS B(x)

方案2

用SQL SERVER WITH 递归查询

1 WITH roy

2 AS ( SELECT Col1 ,

3 COl2 = CAST(LEFT(Col2, CHARINDEX(',', Col2 + ',') - 1) AS NVARCHAR(100)) ,

4 Split = CAST(STUFF(COl2 + ',', 1,

5 CHARINDEX(',', Col2 + ','), '') AS NVARCHAR(100))

6 FROM Tab

7 UNION ALL

8 SELECT Col1 ,

9 COl2 = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)) ,

10 Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100))

11 FROM Roy

12 WHERE split > ''

13 )

14 SELECT COl1 ,

15 COl2

16 FROM roy

17 ORDER BY COl1

18 OPTION ( MAXRECURSION 0 )