How to Efficiently Split Strings in SQL Server 2008 R2?

The string in the efficient segmentation SQL Server 2008 R2

Question:

The use of separators (such as comma) segmentation string is common needs in data processing. However, many effective solutions in the higher version of SQL Server are not suitable for SQL Server 2008 R2.

Solution:

For SQL Server 2008 R2, a reliable method is to use T-SQL to create a custom user definition function (UDF). The following UDF called DBO.SplitString can be used to divide string according to the specified separators:

To use this UDF, just pass the string to be divided as a parameter, as shown below:

<code class="language-sql">CREATE FUNCTION dbo.splitstring (@stringToSplit VARCHAR(MAX))
RETURNS @returnList TABLE ([Name] [nvarchar](500))
AS
BEGIN
    DECLARE @name NVARCHAR(255)
    DECLARE @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>

This will return a table, which contains the components of the original string, which is separated by commas.

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

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