SQL逗号分割转成多行|极客教程

SQL逗号分割转成多行

在数据库中，经常会遇到需要将一行数据中逗号分割的内容转成多行的情况，这在数据清洗或者数据处理中是一个常见的操作。本文将详细介绍如何使用SQL来实现逗号分割转成多行的功能。

1. 使用`STRING_SPLIT`函数

在SQL Server 2016及以上版本中，可以使用STRING_SPLIT函数来实现逗号分割转成多行的功能。STRING_SPLIT函数可以将一个字符串按照指定的分隔符进行分割，返回一个表格。接下来我们通过一个示例来演示如何使用STRING_SPLIT函数实现逗号分割转成多行：

DECLARE @str NVARCHAR(MAX) = 'A,B,C,D,E,F';

SELECT value
FROM STRING_SPLIT(@str, ',')

运行上述代码后，会得到如下结果：

value
------
A
B
C
D
E
F

在上面的示例中，我们首先定义了一个字符串'A,B,C,D,E,F'，然后使用STRING_SPLIT函数将其按逗号分隔成多行。最终得到了各个分割后的值。

2. 使用自定义函数实现逗号分割转成多行

在一些没有STRING_SPLIT函数的数据库系统中，我们可以通过自定义函数来实现逗号分割转成多行的功能。下面是一个使用递归方法编写的自定义函数示例：

CREATE FUNCTION dbo.SplitString
(
    @str NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @result TABLE (value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @pos INT
    DECLARE @len INT

    WHILE CHARINDEX(@delimiter, @str) > 0
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @str)
        SET @len = LEN(@str) - LEN(REPLACE(@str, @delimiter, ''))

        INSERT INTO @result
        VALUES (SUBSTRING(@str, 1, @pos - 1))

        SET @str = SUBSTRING(@str, @pos + 1, @len)
    END

    INSERT INTO @result
    VALUES (@str)

    RETURN
END

接下来我们通过调用自定义的SplitString函数来实现逗号分割转成多行的功能：

DECLARE @str NVARCHAR(MAX) = 'X,Y,Z';

SELECT value
FROM dbo.SplitString(@str, ',')

运行上述代码后，会得到如下结果：

value
------
X
Y
Z

上述代码中，我们首先创建了一个自定义函数SplitString，然后通过调用该函数将字符串'X,Y,Z'按逗号进行分割转成多行。

3. 考虑空格和去除重复值

在实际的数据处理中，有时候我们还需要考虑到输入字符串中可能存在空格或者需要去除重复值的情况。可以通过在自定义函数中增加一些逻辑来实现这些功能。下面是对自定义函数进行修改，进一步考虑空格和去除重复值的逻辑：

CREATE FUNCTION dbo.SplitString
(
    @str NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @result TABLE (value NVARCHAR(MAX))
AS
BEGIN
    DECLARE @pos INT
    DECLARE @len INT

    SET @str = REPLACE(@str, ' ', '') -- 去除空格

    WHILE CHARINDEX(@delimiter, @str) > 0
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @str)
        SET @len = LEN(@str) - LEN(REPLACE(@str, @delimiter, ''))

        INSERT INTO @result
        VALUES (SUBSTRING(@str, 1, @pos - 1))

        SET @str = SUBSTRING(@str, @pos + 1, @len)
    END

    INSERT INTO @result
    VALUES (@str)

    DELETE FROM @result
    WHERE value = '' -- 去除空值

    RETURN
END

通过这样的修改，我们可以在处理逗号分割转成多行的过程中去除空格和重复的空值。