SQL Server String List Parse Function

Most modern web languages these days offer some function to parse a "list" of text items, which is usually a text string with multiple values separated by a delimiter, often a comma. SQL Server unfortunately does not have such an affordance, which becomes troublesome when you're passing a dynamic number of parameters into a SQL statement.

Here comes a user-defined function, which takes a string and a delimiter, and returns a table with the text items broken out into separate rows. To use it:

    select t.theString    from dbo.fn_ParseStringList('1,2,3', ',') t    order by t.theString

And here's the code:

    create function [dbo].[fn_ParseStringList] (      @input varchar(8000),      @delim char(1)      )    returns @output table (      theString varchar(200)      )    as    begin      -- append the delimiter to the end of the string      set @input = @input + @delim      -- what is the position where the delimiter is located      declare @pos int      set @pos = charIndex(@delim, @input, 1)      while (@pos > 0) begin        if (@pos > 1)           insert into @output (theString)          select left(@input, @pos-1)        set @input = right(@input, len(@input)-@pos)        set @pos = charIndex(@delim, @input, 1)        end      return      end    GO