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

Transact-SQL doesn't have arrays or nifty string functions like the C

 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

Leo: 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

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

BlueFoot: 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!

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

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

Biljana: Thanks a lot, exactly what i needed :)

Franz: Thanky also. Did this several times with hardcoded fieldnames and ID's etc. Now it's streamline.

Posted: 07 May 2007