Correction to parsestring
The original function fails when a mistaken split char is being snet to the function.
This can easly be corrected by adding an if to check the value of the @holdpos inside the loop before sending it to the substring function
if the value is 0 then break the loop
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)
-- Added by Gil Adi
if @holdpos<>0 -- this if and else
begin
insert @tabsplit
select SUBSTRING (@txtarray, @holdpos2, @holdpos - @holdpos2)
Set @holdpos2 = @holdpos + 1
Set @loopcount = @loopcount + 1
end
else
break
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
SELECT * FROM [master].[dbo].[parsestring]('abcd,efg,hij,klm,no,pq,',',',6)
SELECT * FROM [master].[dbo].[parsestring]('abcd,efg,hij,klm,no,pq',',',10)
SELECT * FROM [master].[dbo].[parsestring]('abcd,efg,hij,klm,no,pq','#',4)
