SQL Server String List Parse Function

Posted 03/14/2016 4:24 PM by Corey Klass

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