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

Splitting Delimited Values in a SQL Column into Multiple Rows

In situations where a SQL column contains delimited data, it may be necessary to extract and format the values into multiple rows. This task becomes especially challenging when dealing with large volumes of data or extremely long delimited strings.

Consider the following example:

Source Table

message-id	recipient-address
[email protected]	[email protected]
[email protected]	[email protected]
[email protected]	[email protected];[email protected];[email protected]

Desired Output

message-id	recipient-address
[email protected]	[email protected]
[email protected]	[email protected]
[email protected]	[email protected]
[email protected]	[email protected]
[email protected]	[email protected]

For SQL Server versions 2016 and above, the STRING_SPLIT function provides a straightforward solution:

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

Prior to SQL Server 2016, a custom split function can be created:

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

With the split function in place, the data can be extracted and formatted:

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

By leveraging these techniques, it is possible to efficiently split delimited values into multiple rows, simplifying data formatting and analysis tasks.

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