SQL User Defined Function to Parse a Delimited String

Introduction

One of the challenges that always comes up when dealing with data captured from web sites is parsing multi-value fields. Many web forms have "check all that apply" checkbox forms, and the data from questions of this type is often logged as a single delimited field value. 

For example, the index values from a block of check boxes may be logged to a text file or returned to the database as a string of delimited numbers: '1|3|6|11'

The challenge is to take these delimited values and get them into a format that is meaningful to a database user.  The single field of delimited values has to be broken apart and stored as individual rows in a child table related to the source row in the parent table.

The best way to accomplish this is up front in the web application. If this is not an option then it has to be handled when importing to the database. This SQL Server function gives you a quick way to turn the data trapped in delimited strings into meaningful data.

One of the more intriguing capabilities of SQL Server User Defined Functions (UDF) is the ability to return a table as a result. This UDF uses this feature to return each element in the delimited string as a row in a result table. The result from the function can be used like any other table. It can be included in a multi-table query, or returned directly by the function call.

Code

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
create   function fn_ParseText2Table 
 (
 @p_SourceText  varchar(8000)
 ,@p_Delimeter varchar(100) = ',' --default to comma delimited.

 )
RETURNS @retTable TABLE 
 (
  Position  int identity(1,1)
 ,Int_Value int 
 ,Num_value Numeric(18,3)
 ,txt_value varchar(2000)
 )
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (Clayton_Groom@hotmail.com)
Posted to the public domain Aug, 2004
06-17-03 Rewritten as SQL 2000 function.
 Reworked to allow for delimiters > 1 character in length 
 and to convert Text values to numbers
********************************************************************************
*/
BEGIN
 DECLARE @w_Continue  int
  ,@w_StartPos  int
  ,@w_Length  int
  ,@w_Delimeter_pos int
  ,@w_tmp_int  int
  ,@w_tmp_num  numeric(18,3)
  ,@w_tmp_txt   varchar(2000)
  ,@w_Delimeter_Len tinyint
 if len(@p_SourceText) = 0
 begin
  SET  @w_Continue = 0 -- force early exit

 end 
 else
 begin
 -- parse the original @p_SourceText array into a temp table

  SET  @w_Continue = 1
  SET @w_StartPos = 1
  SET @p_SourceText = RTRIM( LTRIM( @p_SourceText))
  SET @w_Length   = DATALENGTH( RTRIM( LTRIM( @p_SourceText)))
  SET @w_Delimeter_Len = len(@p_Delimeter)
 end
 WHILE @w_Continue = 1
 BEGIN
  SET @w_Delimeter_pos = CHARINDEX( @p_Delimeter
      ,(SUBSTRING( @p_SourceText, @w_StartPos
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)))
      )
 
  IF @w_Delimeter_pos > 0  -- delimeter(s) found, get the value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
        ,(@w_Delimeter_pos - 1)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SET @w_StartPos = @w_Delimeter_pos + @w_StartPos + (@w_Delimeter_Len- 1)
  END
  ELSE -- No more delimeters, get last value

  BEGIN
   SET @w_tmp_txt = LTRIM(RTRIM( SUBSTRING( @p_SourceText, @w_StartPos 
      ,((@w_Length - @w_StartPos) + @w_Delimeter_Len)) ))
   if isnumeric(@w_tmp_txt) = 1
   begin
    set @w_tmp_int = cast( cast(@w_tmp_txt as numeric) as int)
    set @w_tmp_num = cast( @w_tmp_txt as numeric(18,3))
   end
   else
   begin
    set @w_tmp_int =  null
    set @w_tmp_num =  null
   end
   SELECT @w_Continue = 0
  END
  INSERT INTO @retTable VALUES( @w_tmp_int, @w_tmp_num, @w_tmp_txt )
 END
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Usage Examples:

Single Character Delimiter

select * from dbo.fn_ParseText2Table('100|120|130.56|Yes|Cobalt Blue','|')

/*
Position    Int_Value   Num_value            txt_value 
----------- ----------- -------------------- --------------
1           100         100.000              100
2           120         120.000              120
3           131         130.560              130.56
4           NULL        NULL                 Yes
5           NULL        NULL                 Cobalt Blue
*/

Multi-Character Delimiter

select * from dbo.fn_ParseText2Table('Red, White, and, Blue',', ')

/*
Position    Int_Value   Num_value            txt_value 
----------- ----------- -------------------- ----------
1           NULL        NULL                 Red
2           NULL        NULL                 White
3           NULL        NULL                 and
4           NULL        NULL                 Blue 
*/ 

Big Multi-Character Delimiter

select * from dbo.fn_ParseText2Table('Red<Tagname>White<Tagname>Blue','<Tagname>')

/* 
Position    Int_Value   Num_value            txt_value 
----------- ----------- -------------------- ----------
1           NULL        NULL                 Red
2           NULL        NULL                 White
3           NULL        NULL                 and
4           NULL        NULL                 Blue 
*/ 

Unfortunately, the only way to use this to process multiple rows is using a cursor.

Here is an example of what the code inside the cursor block would look like to insert parsed values from a string as rows in a child table

As a table in an insert statement:

create table #tmp_Child (parent_id int, ColorSelection varchar(30), SelOrder tinyint)
declare @parent_id int
 ,@ColorSelections varchar(255)
 ,@delim varchar(100) 
set @parent_id = 122
set @ColorSelections = 'Red, White, and, Blue'
set @delim = ', ' 


-- cursor block starts here

insert #tmp_Child (parent_id, ColorSelection, SelOrder)
select @parent_id
 ,t.txt_value
 ,t.position
from dbo.fn_ParseText2Table(@ColorSelections, @delim) as t 
-- cursor block ends here

select * from #tmp_child 
drop table #tmp_child