How Can I Split Delimited Values in a SQL Column into Multiple Rows?

Splitting Delimited Values in a SQL Column into Multiple Rows

Delimiters in SQL columns can create challenges when attempting to split values into multiple rows. This article provides solutions for both recent and legacy versions of SQL Server.

SQL Server 2016 and Newer

SQL Server 2016 introduces the STRING_SPLIT function, allowing for easy value separation using the following syntax:

SELECT s.[message-id], f.value
FROM dbo.SourceData AS s
CROSS APPLY STRING_SPLIT(s.[recipient-address], ';') as f;

SQL Server Versions Prior to 2016

For older versions of SQL Server, create a split function such as:

CREATE FUNCTION dbo.SplitStrings
(
    @List       NVARCHAR(MAX),
    @Delimiter  NVARCHAR(255)
)
RETURNS TABLE
AS
    RETURN (SELECT Number = ROW_NUMBER() OVER (ORDER BY Number),
        Item FROM (SELECT Number, Item = LTRIM(RTRIM(SUBSTRING(@List, Number, 
        CHARINDEX(@Delimiter, @List + @Delimiter, Number) - Number)))
    FROM (SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id])
        FROM sys.all_objects AS s1 CROSS APPLY sys.all_objects) AS n(Number)
    WHERE Number <= CONVERT(INT, LEN(@List))
        AND SUBSTRING(@Delimiter + @List, Number, 1) = @Delimiter
    ) AS y);
GO

Once the split function is created, you can use it to extract values:

SELECT s.[message-id], f.Item
FROM dbo.SourceData AS s
CROSS APPLY dbo.SplitStrings(s.[recipient-address], ';') as f;

Tip:

For optimal storage and referencing, avoid using dashes in column names, as they require enclosing in square brackets.

The above is the detailed content of How Can I Split Delimited Values in a SQL Column into Multiple Rows?. For more information, please follow other related articles on the PHP Chinese website!