SQL Server：将多个字符串分别分成一行

Question

我是SQL Server的新手，我正在处理以下问题。

假设我有一列看起来像这样：

ID  String
-------------------------
1   Today is a good day!
2   Whatever
3   Hello my friend

所以我的目标是将这些句子分解为：

ID  String1   String2    String3    String4    String5
------------------------------------------------------
1   Today     is         a          good       day!
2   Whatever
3   Hello     my         friend

我尝试使用此代码：

CREATE FUNCTION [dbo].[SplitString] 
     (@str nvarchar(max), 
      @separator char(1))
RETURNS TABLE
AS
    RETURN (
         WITH tokens(p, a, b) AS 
         (
             SELECT
                 CAST(1 AS BIGINT), 
                 CAST(1 AS BIGINT), 
                 CHARINDEX(@separator, @str)

             UNION ALL

             SELECT
                 p + 1, 
                 b + 1, 
                 CHARINDEX(@separator, @str, b + 1)
             FROM 
                 tokens
             WHERE
                 b > 0
         )
         SELECT
             --p-1 ItemIndex,
             SUBSTRING(@str, a, 
                           CASE WHEN b > 0 THEN b-a 
                                ELSE LEN(@str) 
                           END) AS Item
         FROM tokens)
GO

这是我在Stackoverflow上找到的。

它似乎适用于单个字符串，但不适用于多个字符串。并将每个单词放在新行中，如下所示：

Item
Today
is
a
good
day!

那么，如何调整代码，使其达到预期效果呢？

另一个问题是，我真的不知道每个字符串中的单词数。

因此可能会有所不同，例如1个单词到100个单词。

如果有人可以帮助我解决这个问题，我将非常高兴，因为我只是开始学习如何使用SQL。

谢谢！名爵

Answer 1

借助XML：

DECLARE @xml xml

;WITH cte AS (
SELECT *
FROM (VALUES
(1, 'Today is a good day!'),
(2, 'Whatever'),
(3, 'Hello my friend')
) as t(ID, String)
)

SELECT @xml = (
SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(REPLACE(String,' ','</w><w>'),'&','&amp;') + '</w></i>' as xml)
FROM cte
FOR XML PATH('')
)

SELECT  t.v.value('@id','int') as ID,
        t.v.value('w[1]','nvarchar(10)') as String1,
        t.v.value('w[2]','nvarchar(10)') as String2,
        t.v.value('w[3]','nvarchar(10)') as String3,
        t.v.value('w[4]','nvarchar(10)') as String4,
        t.v.value('w[5]','nvarchar(10)') as String5,
        t.v.value('w[6]','nvarchar(10)') as String6
FROM @xml.nodes('/i') as t(v)

输出：

ID          String1    String2    String3    String4    String5    String6
----------- ---------- ---------- ---------- ---------- ---------- ----------
1           Today      is         a          good       day!       NULL
2           Whatever   NULL       NULL       NULL       NULL       NULL
3           Hello      my         friend     NULL       NULL       NULL

编辑

与实际表一起使用：

DECLARE @xml xml

SELECT @xml = (
SELECT CAST('<i id="' + CAST(ID as nvarchar(10)) + '"><w>' + REPLACE(big_string,' ','</w><w>') + '</w></i>' as xml)
FROM [table]
FOR XML PATH('')
)

SELECT  t.v.value('@id','int') as ID,
        t.v.value('w[1]','nvarchar(10)') as String1,
        t.v.value('w[2]','nvarchar(10)') as String2,
        t.v.value('w[3]','nvarchar(10)') as String3,
        t.v.value('w[4]','nvarchar(10)') as String4,
        t.v.value('w[5]','nvarchar(10)') as String5,
        t.v.value('w[6]','nvarchar(10)') as String6,
        t.v.value('w[7]','nvarchar(10)') as String7
FROM @xml.nodes('/i') as t(v)