SQL原数据:
SQL语句写法:
WITH NameCollect
AS
(
SELECT
ID,
SingleName = CAST(LEFT(REPLACE(Name, ' ', ''),CHARINDEX(';',REPLACE(Name,' ', '') + ';')-1) AS NVARCHAR(MAX)) ,
Split = CAST(STUFF(REPLACE(Name, ' ', '') + ';',1,CHARINDEX(';',REPLACE(Name, ' ','') + ';'), '') AS NVARCHAR(MAX))
FROM dbo.Stu
WHERE Name IS NOT NULL
UNION ALL
SELECT
ID,
SingleName = CAST(LEFT(Split,CHARINDEX(';', Split) - 1) AS NVARCHAR(MAX)) ,
Split = CAST(STUFF(Split, 1, CHARINDEX(';', Split), '') AS NVARCHAR(MAX))
FROM NameCollect
WHERE Split > ''
)
SELECT * FROM NameCollect
分割后数据: