Kmlakov's script, t-sql function to parse string data, posted on 1/19/2005, required the passing in text ends with the delimiter. But it is usually not the case. I rewrote the script (added one IF statement). The new script can treat the text with or without ending delimiter. The third parameter will be the number of delimiters. For example, the following two select statements have the same result:
SELECT * FROM [master].[dbo].[parsestring]('abcd,efg,hij,klm,no,pq,',',',6)
SELECT * FROM [master].[dbo].[parsestring]('abcd,efg,hij,klm,no,pq',',',5)
Revise t-sql function to parse string data
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
/******************************************************************************
*File Name:dbo.parsestring
*File Desc:Will split out array data.
*Database:MASTER
*Language:MS Transact-SQL
*Version:MS SQL 2000
*
*Tables:N/A
*
*
*
*Procedures:N/A
*
*Date:1/13/2005
*Author:KILEY MILAKOVIC
*Architect:
*
*Special Comments/Warnings
*None
*
*******************************************************************************
*Function Name:dbo.parsestring
*
*Function Desc:Split out array data.
*
*Parameters:
* @txtarray varchar(2000)= Text to be parsed
* @delimit char(1) = delimiter used to delimIt array data
* @reccount int = number of objects in array
*
*RETURNS: @tabsplit table variable of all parsed values
*
*Notes:
*None
********************************************************************************/
alter FUNCTION dbo.parsestring (@txtarray varchar(2000), @delimit char(1),@reccount int)
RETURNS @tabsplit table(txtout varchar(300))
AS
BEGIN
declare @loopcount int,
@holdpos int,
@holdpos2 int
set @loopcount = 1
set @holdpos2 = 1
set @holdpos = 1
while @loopcount <= @reccount
begin
select @holdpos = charindex(@delimit,@txtarray,@holdpos + 1)
insert @tabsplit
select SUBSTRING (@txtarray, @holdpos2, @holdpos - @holdpos2)
set @holdpos2 = @holdpos + 1
Set @loopcount = @loopcount + 1
end
--Added by Yin Zhang
if @holdpos < len(@txtarray)
begin
insert @tabsplit
select SUBSTRING (@txtarray, @holdpos2, len(@txtarray) - @holdpos2 + 1)
end
--End adding
RETURN
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO