Click here to Skip to main content
Click here to Skip to main content
Go to top

SQL User Defined Function to Parse a Delimited String

, 9 Aug 2004
Rate this:
Please Sign up or sign in to vote.
SQL Function to parse a delimited string and return it as a table. Handles multi-character delimiters and returns the results as a table.

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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Claytong
Web Developer
United States United States
I have been a database design consultant for the past 10 years. My primary focus is designing and bulding data driven, highly automated OLTP and datatwarehouse applications that leverage the Microsoft SQL Server platform.
 
I have worked with SQL Server since version 4.12 and have experience with Oracle, Sybase and DB/2 as well.
 
I would term myself as a Data architect and programmer DBA, with a good dose of system architect and troubleshooter thrown in.

Comments and Discussions

 
GeneralMy vote of 5 PinmemberPeter Mendez28-Sep-12 12:05 
Generalhandling space better. and symbols. Pinmemberroger_279-Sep-10 12:11 
GeneralEnhancement suggestion - space delimiters PinmemberJumpyGoat24-Feb-08 12:18 
I found that the function enters into a never-ending loop when a ' ' (space) is passed as the delimiter. I think this is because the following expression returns zero (0):
 
LEN(' ')
 
I would replace your line of code:
 
SET @w_Delimeter_Len = len(@p_Delimeter)
 
with the following:
 
SET @w_Delimeter_Len = len('x' + @p_Delimeter + 'x') - 2
 
This seems to fix the problem with a space delimiter.
 
Thanks for an excellent function.
GeneralPARSE DATE VALUES: enhancement . (Mejora para procesar fechas delimitadas) Pinmemberlucindom12-May-06 8:13 
GeneralNeed help Pinmemberswbcc8-May-06 9:50 
GeneralYou da man! PinmemberMerlot10-Jan-06 12:01 
GeneralRe: You da man! PinmemberMerlot10-Jan-06 19:40 
GeneralPerfect... PinsussAnonymous4-Oct-05 20:45 
GeneralThank you PinsussAlex osipov13-Jun-05 19:55 
GeneralProcedures.. PinsussAnonymous25-Oct-04 8:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140916.1 | Last Updated 10 Aug 2004
Article Copyright 2004 by Claytong
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid