Click here to Skip to main content
15,867,141 members
Articles / Database Development / SQL Server

SQL User Defined Function to Parse a Delimited String

Rate me:
Please Sign up or sign in to vote.
4.85/5 (25 votes)
5 Apr 2016CPOL2 min read 276.4K   49   14
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

Updated!

I've not paid much attention to this since I posted it years ago, but based on a couple requests that came through, and new features available in SQL Server, I thought it deserved a quick refresh.

Particularly, a test for Date values and a DATETIME column have been added. Also a version that uses the XML functions in SQL server to parse the string has been added. The XML approach is more efficient and scales better.

Cheers!

Clayton

 

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

CREATE FUNCTION [fn_ParseText2Table]
  (@p_SourceText VARCHAR(MAX)
  ,@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(MAX)
   ,[Date_value] DATETIME
   )
AS
/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
Posted to the public domain Aug, 2004
2003-06-17 Rewritten as SQL 2000 function.
 Reworked to allow for delimiters > 1 character in length
 and to convert Text values to numbers
2016-04-05 Added logic for date values based on "new" ISDATE() function, Updated to use XML approach, which is more efficient.
********************************************************************************
*/


BEGIN
 DECLARE @w_xml xml;
 SET @w_xml = N'<root><i>' + replace(@p_SourceText, @p_Delimeter,'</i><i>') + '</i></root>';


 INSERT INTO @retTable
     ([Int_Value]
    , [Num_Value]
    , [Txt_Value]
    , [Date_value]
     )
     SELECT CASE
       WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST(CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC) AS INT)
      END AS [Int_Value]
    , CASE
       WHEN ISNUMERIC([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST([i].value('.', 'VARCHAR(MAX)') AS NUMERIC(18, 3))
      END AS [Num_Value]
    , [i].value('.', 'VARCHAR(MAX)') AS [txt_Value]
    , CASE
       WHEN ISDATE([i].value('.', 'VARCHAR(MAX)')) = 1
       THEN CAST([i].value('.', 'VARCHAR(MAX)') AS DATETIME)
      END AS [Num_Value]
     FROM @w_xml.nodes('//root/i') AS [Items]([i]);
 RETURN;
END;
GO


--Old version, with some updates...
CREATE FUNCTION [fn_ParseText2Table_old]
 (
 @p_SourceText VARCHAR(MAX)
 , @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(MAX)
       , [Date_value] DATETIME
      )
AS


/*
********************************************************************************
Purpose: Parse values from a delimited string
  & return the result as an indexed table
Copyright 1996, 1997, 2000, 2003 Clayton Groom (<A href="mailto:Clayton_Groom@hotmail.com">Clayton_Groom@hotmail.com</A>)
Posted to the public domain Aug, 2004
2003-06-17 Rewritten as SQL 2000 function.
 Reworked to allow for delimiters > 1 character in length
 and to convert Text values to numbers
2016-04-05 Added logic for date values based on "new" ISDATE() function
********************************************************************************
*/


  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(MAX)
      , @w_date          DATETIME
      , @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;
       IF ISDATE(@w_tmp_txt) = 1
        BEGIN
         SET @w_date = CAST(@w_tmp_txt AS DATETIME);
        END;
       ELSE
        BEGIN
         SET @w_date = 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;
       IF ISDATE(@w_tmp_txt) = 1
        BEGIN
         SET @w_date = CAST(@w_tmp_txt AS DATETIME);
        END;
       ELSE
        BEGIN
         SET @w_date = NULL;
        END;
       SELECT @w_Continue = 0;
      END;
     INSERT INTO @retTable
     VALUES
      (@w_tmp_int
        , @w_tmp_num
        , @w_tmp_txt
        , @w_date
      );
    END;
   RETURN;
  END;
GO

Usage Examples:

Single Character Delimiter

SQL
select * from dbo.fn_ParseText2Table('100|120|130.56|Yes|Cobalt Blue|2016-04-04|2000-06-17','|')
SQL
/*
Position    Int_Value   Num_value            txt_value   Date_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
6           NULL        NULL                 NULL	 2016-04-04 00:00:00.000
7           NULL        NULL                 NULL	 2000-06-17 00:00:00.000


*/

Multi-Character Delimiter

SQL
select * from dbo.fn_ParseText2Table('Red, White, and, Blue',', ')
SQL
/*
Position    Int_Value   Num_value            txt_value  Date_value
----------- ----------- -------------------- ---------- ----------
1           NULL        NULL                 Red	NULL	
2           NULL        NULL                 White	NULL
3           NULL        NULL                 and	NULL
4           NULL        NULL                 Blue 	NULL
*/ 

Big Multi-Character Delimiter

SQL
select * from dbo.fn_ParseText2Table('Red<Tagname>White<Tagname>Blue','<Tagname>')
SQL
/* 
Position    Int_Value   Num_value            txt_value  Date_value
----------- ----------- -------------------- ---------- ----------
1           NULL        NULL                 Red	NULL
2           NULL        NULL                 White	NULL
3           NULL        NULL                 and	NULL
4           NULL        NULL                 Blue 	NULL
*/ 

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:

SQL
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, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Web Developer Clayton Groom, LLC
United States United States
I am a database design consultant with over 20 years experience in consulting.

My primary focus is designing and bulding data driven, highly automated OLTP and datatwarehouse applications that leverage the Microsoft SQL Server platform. I am skilled in SQL Server Analysis Services (MDS and Tabular), MDX, DAX, and all the related technologies. I am experienced with SharePoint Business Intelligence and dashboard capabilities, including installation, configuration, and Kerberos authentication set up.

I have worked with SQL Server since version 4.12 and have experience with Oracle, Sybase, Teradata and DB/2 as well.

I would term myself as a Data Warehouse Architect and programmer DBA, with a good dose of system architect and troubleshooter thrown in.

Comments and Discussions

 
QuestionError Executing the Script Pin
Member 1480915221-Apr-20 8:48
Member 1480915221-Apr-20 8:48 
QuestionBetter solution Pin
martin.nedopil5-Apr-16 20:28
martin.nedopil5-Apr-16 20:28 
GeneralMy vote of 5 Pin
Peter Mendez28-Sep-12 12:05
Peter Mendez28-Sep-12 12:05 
Generalhandling space better. and symbols. Pin
roger_279-Sep-10 12:11
roger_279-Sep-10 12:11 
great help. but let me fix it up a little. my problem is that if the data passed through it contains a - , +, or a $, it tries to make it currency.

it happens here

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

so I found a new function you have to create first called
IsReallyNumeric. it looks like this

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[isReallyNumeric]  
(  
    @num VARCHAR(64)  
)  
RETURNS BIT  
BEGIN  

    IF LEFT(@num, 1) = '-'  
        SET @num = SUBSTRING(@num, 2, LEN(@num))  
 
    DECLARE @pos TINYINT  
 
    SET @pos = 1 + LEN(@num) - CHARINDEX('.', REVERSE(@num))  
 
    RETURN CASE  
    WHEN PATINDEX('%[^0-9.-]%', @num) = 0  
        AND @num NOT IN ('.', '-', '+', '^') 
        AND LEN(@num)>0  
        AND @num NOT LIKE '%-%' 
        AND  
        (  
            ((@pos = LEN(@num)+1)  
            OR @pos = CHARINDEX('.', @num))  
        )  
    THEN  
        1  
    ELSE  
    0  
    END  
END  


execute it. then use the function that has been modified already to handle spaces, and use IsReallyNumeric, instead of IsNumeric. like this:
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER ON
GO
CREATE function [dbo].[fn_ParseText2Table] 
 (
 @p_SourceText  nvarchar(4000)
 ,@p_Delimeter nvarchar(100) = ' ' --default was comma delimited.
 )
RETURNS @retTable TABLE 
 (
  Position  int identity(1,1)
 ,Int_Value int 
 ,Num_value Numeric(18,3)
 ,txt_value nvarchar(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   nvarchar(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) . original way. this new way handles space delimiter. 9/2/2010
  SET @w_Delimeter_Len = len('x' + @p_Delimeter + 'x') - 2 

 end
 WHILE @w_Continue = 1
 BEGIN
  SET @w_Delimeter_pos = CHARINDEX( cast(@p_Delimeter as nvarchar)
      ,(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 (dbo.isReallyNumeric(@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
-- old way   
--   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


execute that. now you have a function that will handle symbols too.
It's not a bug it's a f- oh wait... no..It's a bug.

GeneralEnhancement suggestion - space delimiters Pin
JumpyGoat24-Feb-08 12:18
JumpyGoat24-Feb-08 12:18 
GeneralRe: Enhancement suggestion - space delimiters Pin
SQLMonger7-Apr-16 9:37
SQLMonger7-Apr-16 9:37 
GeneralRe: Enhancement suggestion - space delimiters Pin
Joseph M. Morgan11-Feb-20 10:20
Joseph M. Morgan11-Feb-20 10:20 
GeneralPARSE DATE VALUES: enhancement . (Mejora para procesar fechas delimitadas) Pin
lucindom12-May-06 8:13
lucindom12-May-06 8:13 
GeneralNeed help Pin
swbcc8-May-06 9:50
swbcc8-May-06 9:50 
GeneralYou da man! Pin
Merlot10-Jan-06 12:01
Merlot10-Jan-06 12:01 
GeneralRe: You da man! Pin
Merlot10-Jan-06 19:40
Merlot10-Jan-06 19:40 
GeneralPerfect... Pin
Anonymous4-Oct-05 20:45
Anonymous4-Oct-05 20:45 
GeneralThank you Pin
Alex osipov13-Jun-05 19:55
Alex osipov13-Jun-05 19:55 
GeneralProcedures.. Pin
Anonymous25-Oct-04 8:30
Anonymous25-Oct-04 8:30 

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

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