T-SQL Code: fn_SplitString

Earlier in the week while dealing with a big SQL project for work, I had to come up with a way to have SQL parse a big list of keywords and break them into a table with each keyword occupying a single row.  This way I could pass that table to a WHERE xxxxx IN clause.

After spending some time searching the typical online SQL resource sites for a while, I couldn't really find what I needed.  But I remembered that last year I had come across a similar UDF to parse a comma-delimted list of munbers into a table of integer values -- it was called CsvToInt from SQLTeam.com (one of may favorite SQL sites).  It was very similar to what I needed to do now, so used it as a model to create a new UDF that solved my problem.  Here's the situation...

I needed to turn a text field like this...

cad cam aec gis eda

...into a table like this...

StrValues
----------------
cad
cam
aec
gis
eda

I decided to make it flexible enough to handle different delimiter characters and also to make it optional to return empty flields.  Here's the code...

/*==========================================================
      Function: fn_SplitString
         Input: @DelimitedList - Delimited string
                @Delimiter - Delimiter character
                @AllowNullFields - 1 = allow, 0 = disallow
       Returns: table
   Description: Parses a delimited string and returns a table of the values...
==========================================================*/
ALTER Function dbo.fn_SplitString (
  @DelimitedList varchar(8000),
  @Delimiter char,
  @AllowNullFields bit = 0
) 
RETURNS @tblListValues table (StrValue varchar(250))
AS

BEGIN
  DECLARE @intDelimiterPos int 
  DECLARE @strListValue varchar(1000)

  SET @DelimitedList = @DelimitedList + @Delimiter

  -- parse the list...
  WHILE PATINDEX('%' + @Delimiter + '%', @DelimitedList) <> 0
  BEGIN
    -- determine the locatio of the next delimiter...
    SELECT @intDelimiterPos = PATINDEX('%' + @Delimiter + '%', @DelimitedList)

    -- retrieve the next value...
    SELECT @strListValue = LEFT(@DelimitedList, @intDelimiterPos - 1)

    -- insert the extracted value into the return table...
    INSERT @tblListValues VALUES (@strListValue)

    -- prepare the delimited string for the next pass...
    SELECT @DelimitedList = STUFF(@DelimitedList, 1, @intDelimiterPos, '')
  END
  -- remove blank fields if necessary...
  IF (@AllowNullFields = 0)
    DELETE FROM @tblListValues WHERE StrValue = ''

  RETURN
END
----------------------

I'm sure real SQL-heads target=_blank would be able to make it more efficient, but it works for my needs right now.  Perhaps someone else will find it useful.  Please let me know if you have a better solution to the problem, or find flaws in my logic.

Thanks again to the guys at SQLTeam.com for providing such a great SQL site.