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) 
