A little while back I had cause to take in a string of delimited values and return a table.  This morning I had a request for the same code, so I figured that I'd put it out here for all to see.  Some of you may already have your own version of this code, but here's mine.

The function below takes in a single string (i.e. 'Fred, Barney, Wilma, Betty') and a delimiter character as parameters and returns the values as a table which can then be sorted, searched, etc.  It will return 2 columns (ID which is an identity and DATA which is your list items). 

You would use it like this:
    SELECT * FROM fn_SplitString('Fred, Barney, Wilma, Betty', ',' )

          

Here's the code for the function:

CREATE FUNCTION fn_SplitString (@Data varchar(8000), 
                                @Delimiter varchar(10))

RETURNS @MyTable TABLE
    (
    Id int identity(1,1),
    Data nvarchar(100)
    )

AS

BEGIN
    Declare @Count int
    Set @Count = 1

    While (Charindex(@Delimiter, @Data) >0)
    Begin
        Insert Into @MyTable (Data)
        Select Data = ltrim(rtrim(Substring
            (@Data,1,Charindex(@Delimiter,@Data)-1)))

        Set @Data = Substring(@Data,Charindex
            (@Delimiter,@Data)+1,len(@Data))
        Set @Count = @Count + 1
    End

    Insert Into @MyTable (data)
    Select Data = ltrim(rtrim(@Data))

    Return

END

Enjoy!

Chris Antoniak  DBA/Developer