SQL Server：将多个字符串拆分为每行一个字符串

Question

SQL Server：将多个字符串拆分为每行一个字符串

3

我是一名新手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。

谢谢！ MG

- mgruber

1

看起来像是学校的作业。如果没有列数限制，你必须使用动态查询。 - xdd

以下是一些性能更好的分割选项。http://sqlperformance.com/2012/07/t-sql-queries/split-strings 要将其转换回列，您需要使用动态交叉表或动态透视表。 - Sean Lange

对于刚学习T-SQL的人来说，你正在处理一个相当高级的问题。 - Sean Lange

@SeanLange 这是一个ETL类。 ;) - nicomp

2个回答

1

我编辑了你的代码。尝试运行这个。列是根据要附加的行动态创建的。结果如下：return rows 它不是按顺序的。尝试编辑上面的代码以包含您的Id，以便再次按顺序排列。

DECLARE @TBL TABLE (Id int, Description varchar(max))
CREATE table #tblResult(Description varchar(max))

INSERT INTO @TBL
SELECT 1, 'Today is a good day!'

DECLARE @separator varchar(1) = ' ', @str varchar(max)
SELECT @str = Description FROM @TBL


     ;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
     )

     INSERT INTO #tblResult
     SELECT
         SUBSTRING(@str, a, 
                       CASE WHEN b > 0 THEN b-a 
                            ELSE LEN(@str) 
                       END) AS Item
                       FROM tokens

    DECLARE @x nvarchar(MAX), @query nvarchar(MAX)

    select @x = STUFF((SELECT ',' + QUOTENAME(Description) 
                from #tblResult
                group by Description
                order by Description
        FOR XML PATH(''), TYPE
        ).value('.', 'nvarchar(MAX)') 
    ,1,1,'')


    set @query = N'SELECT ' + @x + N' from 
         (
            select Description
            from #tblResult
        ) x
        pivot 
        (
            max(Description)
            for Description in (' + @x + N')
        ) p '

  exec sp_executesql @query;


  drop table #tblResult

- user6448704

这段代码似乎也能工作，但输出顺序不正确！？ - mgruber

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- gofr1 · Accepted Answer

通过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)