将字符串转换成Table

例子一：

原始内容如下：
 代码 ；              数据内容：
   1                 q换行符b 换行符 c
   2                  w换行符v 换行符 n
   3                  x换行符m换行符k

想要的样子，可以创建一个新表：
   1   q
   1   b
   1    c
   2    w
   2   v
   2   n
   3   x
   3   m
   3   k

 

;WITH a1 (id,cstr) AS
(
SELECT 1,'q换行符b 换行符c' 
UNION all SELECT 2,'w换行符v 换行符n'
UNION all SELECT 3,'x换行符m换行符k'
)
,a2 AS 
(
SELECT id,REPLACE(cstr,' ','') cstr FROM a1
)

SELECT a.id,b.cstr
FROM
(SELECT id, cstr=CONVERT(XML,'<root><item>'+replace(RTRIM(LTRIM(cstr)),'换行符','</item><item>')+'</item></root>') FROM a2) a
OUTER APPLY
(SELECT cstr = C.ColName.value('.','VARCHAR(5)') FROM a.cstr.nodes('/root/item') C(ColName)) b  -->ColName定义列名

/*
id	cstr
1	q
1	b
1	c
2	w
2	v
2	n
3	x
3	m
3	k
*/

 

例子二：一个字符串分割成一个表

 

方法一：

CREATE TABLE test(id INT,[key] NVARCHAR(1000))
INSERT INTO test
VALUES(1,'车位地址1,车态位状1|车位地址2,车位状态2|车位地址3,车位状态3|车位地址n,车位状态n')

;WITH cte AS 
(
SELECT distinct SUBSTRING([key],number,CHARINDEX('|',[key]+'|',number)-number) as [key]
from test a, master..spt_values 
where number >=1 and type='p'  
	and number<len([key])  and substring('|'+[key],number,1)='|'
)
    
SELECT SUBSTRING([key],1,CHARINDEX(',',[key],1)-1)  AS BerthCode,
	SUBSTRING([key],CHARINDEX(',',[key],1)+1,LEN([key])) AS [BerthStatus]
FROM cte
DROP TABLE test

/*
BerthCode       BerthStatus      
------------- -------------------
车位地址1	车态位状1
车位地址2	车位状态2
车位地址3	车位状态3
车位地址n	车位状态n

*/

 方法二：

declare @s nvarchar(1000)
set @s='车位地址1,车态位状1|车位地址2,车位状态2|车位地址3,车位状态3|车位地址n,车位状态n'
set @s=REPLACE(@s,'|','</item><item>')

SELECT SUBSTRING(b.[key],1,CHARINDEX(',',b.[key],1)-1)  AS BerthCode,
	SUBSTRING(b.[key],CHARINDEX(',',b.[key],1)+1,LEN(b.[key])) AS [BerthStatus]
FROM (SELECT [key]=CONVERT(XML,'<root><item>'+@s+'</item></root>')) a
OUTER APPLY
	(SELECT [key] = C.ColName.value('.','VARCHAR(100)') FROM a.[key].nodes('/root/item') C(ColName)) b  -->ColName定义列名

/*
BerthCode	BerthStatus
----------------------
车位地址1	车态位状1
车位地址2	车位状态2
车位地址3	车位状态3
车位地址n	车位状态n
*/