How Can I Efficiently Split Comma-Delimited Strings in SQL Server 2008 R2?

T-SQL String Splitting in SQL Server 2008 R2

SQL Server 2008 R2 lacks built-in string splitting functionality, making comma-separated string processing challenging. This article demonstrates a robust solution using a custom function.

Creating a Custom Split Function

The following dbo.splitstring function efficiently splits comma-delimited strings:

<code class="language-sql">CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX))
RETURNS @returnList TABLE ([Name] [nvarchar](500))
AS
BEGIN
    DECLARE @name NVARCHAR(255), @pos INT;
    WHILE CHARINDEX(',', @stringToSplit) > 0
    BEGIN
        SELECT @pos = CHARINDEX(',', @stringToSplit);
        SELECT @name = SUBSTRING(@stringToSplit, 1, @pos - 1);
        INSERT INTO @returnList SELECT @name;
        SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + 1, LEN(@stringToSplit) - @pos);
    END
    INSERT INTO @returnList SELECT @stringToSplit;
    RETURN;
END;</code>

Function Usage

To use the function, simply pass the string to be split as a parameter:

<code class="language-sql">SELECT * FROM dbo.splitstring('91,12,65,78,56,789');</code>

This will produce a table with each comma-separated value in a separate row:

| Name | |---|---| | 91 | | 12 | | 65 | | 78 | | 56 | | 789 |

This custom function provides a reliable method for handling comma-delimited strings within the limitations of SQL Server 2008 R2.

The above is the detailed content of How Can I Efficiently Split Comma-Delimited Strings in SQL Server 2008 R2?. For more information, please follow other related articles on the PHP Chinese website!