Splitting Comma Separated Values into Rows in SQL Server

In this article, we’ll explore the process of splitting comma separated values into individual rows using SQL Server. We’ll examine the current issue with the provided query and discuss potential solutions to achieve the desired output.

Current Issue with the Provided Query

The original query aims to split two columns ListType_ID and Values in a table, which contain comma separated values. The intention is to convert these comma separated strings into individual rows while preserving their corresponding IDs from other columns.

However, the provided query has an issue. It uses an XML-based approach with PARSENAME and REPLACE, but this leads to incorrect results due to the way the function splits the string.

The problem lies in how SQL Server handles the XML parsing. When using PARSENAME on the resulting split values, it only takes into account the first part of the value (i.e., before the next /M/). As a result, both ListType_ID and Values end up with identical values.

Understanding SQL Server Split Functions

To accurately handle comma separated strings, we need to use user-defined split functions. There are several approaches available for this task.

Using STRING_SPLIT

SQL Server introduced the STRING_SPLIT function in version 2016 (SQL Server 12.0). This function can be used to split a string into an array of substrings based on a specified separator.

Here’s how you could modify the original query using STRING_SPLIT:

SELECT 
Rule_ID,
idlist.val as ListType_ID,
valueslist.val as [Values]
FROM Table_1
CROSS APPLY dbo.SPLIT(ListType_ID, ',') as idlist
CROSS APPLY dbo.SPLIT([Values], ',') as valueslist
WHERE 
idlist.id = valueslist.id

In this modified query, we use STRING_SPLIT to break the strings into individual parts. The resulting arrays are then used in separate cross-apply operations.

Custom SQL Server Split Function

For older versions of SQL Server (before version 2016), you can create your own custom split function using the following approach:

CREATE FUNCTION dbo.SPLIT (@string VARCHAR(MAX), @separator CHAR(1))
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN 
(
    SELECT value = LEFT(@string, CHARINDEX(@separator, @string))
    FROM Table_1
    WHERE @string = ''
    UNION ALL
    SELECT value = SUBSTRING(@string, CHARINDEX(@separator, @string) + 1, LEN(@string) - CHARINDEX(@separator, @string))
    FROM dbo.SPLIT (@string, @separator)
)
GO

You can then use this custom function in the original query:

SELECT 
Rule_ID,
idlist.val as ListType_ID,
valueslist.val as [Values]
FROM Table_1
CROSS APPLY dbo.SPLIT(ListType_ID, ',') as idlist
CROSS APPLY dbo.SPLIT([Values], ',') as valueslist
WHERE 
idlist.id = valueslist.id

This custom function iteratively selects the first part of the string until it reaches the separator, then moves on to the next part.

Conclusion

In this article, we explored the challenge of splitting comma separated strings into individual rows using SQL Server. We examined an original query that used XML parsing but resulted in incorrect output and discussed alternative approaches using user-defined functions like STRING_SPLIT or custom split functions.

By applying one of these solutions, you can successfully transform comma separated values into separate rows while maintaining the integrity of your data.

For any questions or suggestions regarding the content of this article, please don’t hesitate to let us know!

Last modified on 2025-01-11