Function to parse a delimited string
This UDF takes a delimited string and parses it into "words" which are returned as rows in a table. The table returned indicates the position of each element in the source string, and converts values to integer and numeric formats if possible.
The script contains examples on how to use the function.
the original version was written as a stored procedure that worked under SQL Server 6.5. If there is interest, I can upload it as well.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
create function fn_ParseText2Table
(
@p_SourceText varchar(8000)
,@p_Delimetervarchar(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_Continueint
,@w_StartPosint
,@w_Lengthint
,@w_Delimeter_posint
,@w_tmp_intint
,@w_tmp_numnumeric(18,3)
,@w_tmp_txt varchar(2000)
,@w_Delimeter_Lentinyint
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
-- Single Character Delimeter
select * from dbo.fn_ParseText2Table('100|120|130.56|Yes|Cobalt Blue','|')
/*
----------- ----------- -------------------- --------------
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 Delimeter
select * from dbo.fn_ParseText2Table('Red, White, and, Blue',', ')
/*
----------- ----------- -------------------- ----------
1 NULL NULL Red
2 NULL NULL White
3 NULL NULL and
4 NULL NULL Blue
*/
-- Multi-Character Delimeter
select * from dbo.fn_ParseText2Table('Red<Tagname>White<Tagname>Blue','<Tagname>')
/*
----------- ----------- -------------------- ----------
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 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 = ', '
insert #tmp_Child (parent_id, ColorSelection, SelOrder)
select @parent_id
,t.txt_value
,t.position
from dbo.fn_ParseText2Table(@ColorSelections, @delim) as t
select * from #tmp_child
drop table #tmp_child 
