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

Splitting Delimited Values in a SQL Column into Multiple Rows

When dealing with tables containing delimited values in a single column, such as a recipient-address field separated by semicolons (;), it becomes necessary to split these values into multiple rows for further processing or analysis. This article explores various methods to achieve this using SQL, particularly focusing on optimizing performance and efficiency.

Using STRING_SPLIT Function for SQL Server 2016

If you are using SQL Server 2016 or later, you can leverage the STRING_SPLIT function to efficiently split delimited values. The syntax for this function is as follows:

STRING_SPLIT(@list, @delimiter)

Here's how you can use STRING_SPLIT to extract recipient addresses into separate rows:

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

Creating a Split Function for Pre-SQL Server 2016 Versions

For SQL Server versions prior to 2016, you can create a custom split function. One example of such a function is:

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

You can then use this function to split delimited values as follows:

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

Optimization and Considerations

To ensure optimal performance, consider the following tips:

• Use the appropriate method based on your SQL Server version.
• Avoid using the LIKE operator to search for delimiters, as this can be inefficient.
• Index the recipient-address column for faster lookups.
• Divide large datasets into smaller chunks for processing.
• Use SQL Server's Bulk Insert mechanisms to efficiently import data into the database.

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