教你如何将SQL Server一个字符串拆分多行显示或者多行数据合并成一个字符串

介绍

在SQL Server开发中，经常需要将一个字符串拆分成多行显示或者将多行数据合并成一个字符串。这是因为在实际应用中，通常会遇到需要进行字符串分割或拼接的情况，例如从一张表中某个字段中选择一些特定的数据进行后续处理，或者将多条数据合并成为一条记录存储。本文将会介绍如何在SQL Server中实现这种字符串操作。

将一个字符串拆分多行显示

使用STUFF和XML PATH

STUFF函数通常用来对字符串进行插入、删除或替换等操作，而XML PATH则是将关系型数据按照XML格式输出。两者结合使用，可以实现将一个字符串拆分成多行显示的功能。

DECLARE @str VARCHAR(50)
SET @str='apple,banana,orange,grape'
SELECT STUFF(
           (SELECT ','+ fruit 
            FROM
            (
              SELECT Split.a.value('.','VARCHAR(100)') fruit
              FROM 
              (SELECT CAST(''+REPLACE(@str,',','')+'' AS XML) AS Data) AS A 
              CROSS APPLY Data.nodes('/x') AS Split(a)
            ) t FOR XML PATH('')
           )
         ,1,1,'') AS result

上述SQL语句中，将字符串"apple,banana,orange,grape"中的逗号全部替换成了XML中的节点，再使用CROSS APPLY和nodes()函数对这些节点进行遍历，将节点的值取出返回至结果集中，最后使用STUFF函数将逗号替换为空格。执行上述SQL语句得到结果如下：

applebananaorangegrape

使用字符串函数

除了使用XML PATH外，我们还可以使用字符串函数来将一个字符串拆分成多行显示。SQL Server 2016之前，SQL Server并没有提供专门的字符串分割函数，因此常用的方法是联合使用CHARINDEX、SUBSTRING和LEN等函数。

DECLARE @str VARCHAR(50)
SET @str='apple,banana,orange,grape'
;WITH Num1 (n) AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n+1 AS n
    FROM Num1 
    WHERE n <= LEN(@str)
),
Num2 (n) AS (
    SELECT 1 AS n
    UNION ALL 
    SELECT n+1 AS n
    FROM Num2 
    WHERE n <= (SELECT MAX(n) FROM Num1)
)
SELECT SUBSTRING(@str,n,CHARINDEX(',',@str+',',n)-n) AS result
FROM Num2
WHERE SUBSTRING(','+@str,n,1) = ','
OPTION (MAXRECURSION 0)

上述SQL语句中，定义了一个公用表表达式Num1和Num2，分别用于生成从1到字符串长度的连续数字和从1到最长分割后子串长度的连续数字，在SELECT语句中使用函数SUBSTRING、CHARINDEX通过计算得出子串并返回。执行上述SQL语句得到结果如下：

applebananaorangegrape

使用JSON函数

从SQL Server 2016开始，SQL Server提供了内置的STRING_SPLIT函数，可以直接将一个字符串拆分成多行显示，并且返回结果集有多列。可以通过选项JSON来进行格式化。

DECLARE @str VARCHAR(50)
SET @str='apple,banana,orange,grape'
SELECT value
FROM STRING_SPLIT(@str, ',') 

上述SQL语句中，使用STRING_SPLIT函数将字符串"apple,banana,orange,grape"分割成多行展示，返回结果集有一列value不存在需使用STUFF函数将分割符转换为空格。执行上述SQL语句得到结果如下：

applebananaorangegrape

多行数据合并成一个字符串

使用STUFF和FOR XML PATH

在SQL Server开发中，可以通过FOR XML PATH来将多条数据合并成一个字符串，同时使用STUFF转换分隔符。

SELECT STUFF((
    SELECT ',' + fruit 
    FROM FruitTable
    FOR XML PATH('')
), 1, 1, '') AS result

上述SQL语句中，将FruitTable表中所有的fruit字段值以逗号隔开后返回结果，最后使用STUFF函数将逗号替换为空格。执行上述SQL语句得到结果如下：

apple,banana,orange,grape

使用COALESCE和FOR XML PATH

在SQL Server开发中，也可以通过COALESCE函数将多条数据合成一个字符串。

DECLARE @str VARCHAR(MAX)
SELECT @str = COALESCE(@str+',','') + fruit 
FROM FruitTable
SELECT @str AS result

上述SQL语句中，使用COALESCE函数将FruitTable表中所有的fruit字段值合并成字符串，中间以逗号隔开。执行上述SQL语句得到结果如下：

apple,banana,orange,grape

使用XML PATH和STUFF函数

类似于将字符串拆分成多行的方法，也可以使用XML PATH和STUFF函数将多行数据合并成一个字符串。

SELECT STUFF((
    SELECT ','+fruit 
    FROM FruitTable
    FOR XML PATH('')
), 1, 1, '') AS result

上述SQL语句中，将FruitTable表中所有的fruit字段值以逗号隔开后返回结果，最后使用STUFF函数将逗号替换为空格。执行上述SQL语句得到结果如下：

apple,banana,orange,grape

总结

本文介绍了如何在SQL Server中实现将一个字符串拆分成多行显示或者将多行数据合成一个字符串。可以根据具体业务需求选择不同的方法，例如在SQL Server 2016之前使用字符串函数进行字符串分割，或者使用内置的STRING_SPLIT函数；在多行数据合并成一个字符串时，可以使用XML PATH和STUFF函数、COALESCE函数等方法完成。对于字符串操作，需要根据不同的应用场景选择合适的方法，为数据处理提供更好的效率和易用性。