Technical Article

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)

Rate

Share

Share

Rate