Technical Article

Parse String into Table

,

This script takes a string of up to 8000 characters with a single character delimtier and returns a table with each substring in its own row. A identity column is also returned so you can easily find the n'th item in the list.

Script is similar to kmlakov's but doesn't require the item count and adds the ID in the result table.

To get all items:
SELECT * FROM master.dbo.f_reuParseList('aa,bb,cc,dd', ',')

To get the 3rd item:
SELECT * from master.dbo.f_reuParseList('aa,bb,cc,dd'), ',') where listid = 3

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO


/******************************************************************************
*File Name:dbo.f_reuParseList
*File Desc:Will split delimited list into table..
*Database:MASTER
*Language:MS Transact-SQL 
*Version:MS SQL 2000
*
*Tables:N/A
*
*
*  
*Procedures:N/A
*
*Date:10/7/2002
*Author:Bill Grover, EU Services, Inc.
*Architect:
*
*Special Comments/Warnings
*None
*
*******************************************************************************
*Function Name:dbo.f_reuParseList
*
*Function Desc:Split a delimited list into a table.
*
*Parameters:
* @tcList varchar(8000)= Text to be parsed
*     @tcDelimiterchar(1) = Text delimiter  
*
*RETURNS: @parsedList table variable of all parsed values with unique ID
*
*Notes:
*None
********************************************************************************/
CREATE FUNCTION f_reuParseList (@tcListVARCHAR(8000), 
@tcDelimiterchar(1))

RETURNS @ParsedList TABLE (ListID int IDENTITY, 
ListItem varchar(8000))

AS
BEGIN
DECLARE @lcListWorkvarchar(8000), 
@lnCommaPosint, 
@lcItemvarchar(8000)

SET @lcListWork = @tcList

WHILE LEN(@lcListWork) > 0
BEGIN
SET @lnCommaPos = CHARINDEX(@tcDelimiter, @lcListWork)
IF @lnCommaPos > 0
BEGIN
SET @lcItem = SUBSTRING(@lcListWork, 1, @lnCommaPos - 1)
SET @lcListWork = SUBSTRING(@lcListWork, @lnCommaPos + 1, LEN(@lcListWork) - @lnCommaPos)
END
ELSE
BEGIN
SET @lcItem = @lcListWork
SET @lcListWork = ''
END

INSERT INTO @ParsedList
VALUES (@lcItem)
END

RETURN
END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Rate

Share

Share

Rate