Revise t-sql function to parse string data

,

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)

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

Rate

5 (1)

Share

Share

Rate

5 (1)