May 07

Split()-like string-to-array function in Transact-SQL

Uncategorized Add comments

Transact-SQL doesn’t have arrays or nifty string functions like the C# System.String.Split () or java’s String.split() returning arrays in one swoop. Then again, SQL knows the SELECT … IN statement like

SELECT * FROM table1 WHERE id in (1,2,3,526) 

so why no looping through comma-delimited strings?

A “Table-valued User-defined Function” returning a table variable is a handy and versatile workaround letting you query and loop through the values with near array-like ease.

The code could look like this:

CREATE FUNCTION [dbo].[StringToTable]
(
@inputString nvarchar(max),
@separator char (1)
)
RETURNS @ResultTable TABLE ( [String] nvarchar(max) )
AS
BEGIN

    DECLARE @stringToInsert nvarchar (max) 

    WHILE LEN(@inputString) > 0
    BEGIN
        SET @StringToInsert      = LEFT(
                                @inputString,
                                ISNULL(NULLIF(CHARINDEX(@separator, @inputString) - 1, -1),
                                LEN(@inputString)
                                )
                                )
        SET @InputString = SUBSTRING(@InputString,

                                     ISNULL
                                     (NULLIF
                                     (CHARINDEX(@separator, @InputString),
                                     0),
                                     LEN(@InputString)) + 1,
                                     LEN(@InputString))

        INSERT INTO @ResultTable
        (
        [String]
        )
        VALUES
        (
        @StringToInsert
        )

        END

    RETURN
END

Again, the Transact-SQL string functions seem clumsy and inadequate compared with high-level languages. Short of using CLR functions in the database table-valued functions can put custom string operations at your immediate disposal.

[Slashdot] [Digg] [Reddit] [del.icio.us] [Facebook] [Technorati] [Google] [StumbleUpon]

5 Responses to “Split()-like string-to-array function in Transact-SQL”

  1. Leo Says:
    May 29th, 2008 at 8:28 pm

    Man, thank you very much for this. I was looking so hard for something like this and I found your site.
    Thanx a lot
    Leo

  2. Ash Says:
    May 28th, 2009 at 12:56 pm

    Nice post, I was looking for this SQL function today.

    Thanks.

    Ash

  3. Praveen Says:
    June 4th, 2010 at 6:42 pm

    Here is the same but slightly different version.
    http://praveenbattula.blogspot.com/2010/06/best-split-udf-function-with-delimeter.html

  4. BlueFoot Says:
    August 25th, 2010 at 7:53 pm

    Thank you so much for this function, helped out so much. Normally it wouldn’t be useful as a good db wont have a comma list in one of the columns or values, but you run across it sometimes… thank!

  5. George Says:
    December 8th, 2011 at 8:44 am

    Thanks for the great read. It was exactly what I was looking for.

Leave a Reply

WP Theme & Icons by N.Design Studio
Entries RSS Comments RSS Log in