Parse a character seperated list of items

Hey,
Just spent WAY to long trying to parse out a item from a comma
seperated list without using temp tables. I bet there is something out
there already, but I couldn't find it. Here's my code for anyone that
wants it. The function parameters are the list of items, the seperator
you are looking for and the index of the item you want. NOTE: The list
is one based. If you enter 0 for the first item it will return NULL. If
the index higher than the number of items, the function also returns 0.
I'm sure the code could be better but I don't have any more time. If
someone improves the code, please post the new stuff so I can have a
look.
Cheers
Russ
CREATE FUNCTION get_list_item
(
@.string_list VARCHAR(8000),
@.seperator CHAR(1) = ',',
@.index_number INT = 1
)
RETURNS VARCHAR(1000)
AS
BEGIN
IF(@.index_number IS NULL)
BEGIN
SET @.index_number = 1
END
DECLARE @.seperator_index INT
DECLARE @.start_position INT
DECLARE @.next_index INT
DECLARE @.item_count INT
DECLARE @.return_item VARCHAR(1000)
DECLARE @.error_occured BIT
SET @.error_occured = 0
SET @.start_position = 0
SET @.item_count = 0
SET @.seperator_index = CHARINDEX(@.seperator, @.string_list)
IF(@.seperator_index > 0)
BEGIN
SET @.item_count = @.item_count + 1
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
WHILE(@.item_count < @.index_number)
BEGIN
SET @.start_position = @.seperator_index + 1
SET @.next_index = CHARINDEX(@.seperator, @.string_list,
@.start_position)
IF(@.next_index > 0)
BEGIN
SET @.seperator_index = @.next_index
SET @.item_count = @.item_count + 1
END
ELSE
BEGIN
--RAISERROR('The index requested was greater than the number of
items in the list', 16, 1)
--SET @.error_occured = 1
BREAK
END
--print 'COUNT = ' + CAST(@.item_count AS VARCHAR) + ' START: ' +
CAST(@.start_position AS VARCHAR) +
--' - END: ' + CAST(@.seperator_index AS VARCHAR)
END
IF(@.item_count = @.index_number - 1)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, (LEN(@.string_list) - @.start_position) + 1)
END
ELSE
BEGIN
IF(@.item_count = @.index_number)
BEGIN
SET @.return_item = SUBSTRING(@.string_list,
@.start_position, @.seperator_index - @.start_position)
END
END
END
ELSE
BEGIN
--DECLARE @.error_message VARCHAR(250)
--SET @.error_message = 'Seperator "' + @.seperator + '" not found.'
--RAISERROR(@.error_message,16,1)
SET @.return_item = NULL
END
RETURN @.return_item
END
P.S. This was tested with SQL Server 2000.
Russ
|||(russ.haley@.gmail.com) writes:
> Just spent WAY to long trying to parse out a item from a comma
> seperated list without using temp tables. I bet there is something out
> there already, but I couldn't find it.
Have a look at http://www.sommarskog.se/arrays-in-sql.html, and
particularly the core function for "Using a Table of Number". Further
down the page, there is also an example with fixed-length input.
I'm not really sure that I see the point with your function. You avoid
creating a table, but since a table is faster for lookup than a list,
I can't see any significant gain with it.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
|||On 20 Mar 2006 12:07:45 -0800, russ.haley@.gmail.com wrote:

>Hey,
>Just spent WAY to long trying to parse out a item from a comma
>seperated list without using temp tables. I bet there is something out
>there already, but I couldn't find it.
Hi Russ,
http://www.sommarskog.se/arrays-in-sql.html
Hugo Kornelis, SQL Server MVP
|||Hi, some time ago I wrote a function for that:
CREATE FUNCTION dbo.Split
(
@.String VARCHAR(200),
@.Delimiter VARCHAR(5)
)
RETURNS @.SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
AS
BEGIN
DECLARE @.SplitLength INT
WHILE LEN(@.String) > 0
BEGIN
SELECT @.SplitLength = (CASE CHARINDEX(@.Delimiter,@.String) WHEN 0 THEN
LEN(@.String) ELSE CHARINDEX(@.Delimiter,@.String) -1 END)
INSERT INTO @.SplittedValues
SELECT SUBSTRING(@.String,1,@.SplitLength)
SELECT @.String = (CASE (LEN(@.String) - @.SplitLength) WHEN 0 THEN ''
ELSE RIGHT(@.String, LEN(@.String) - @.SplitLength - 1) END)
END
RETURN
END
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
|||The point was I couldn't find an easy way to parse a character
seperated list. There are times in an application I am working on where
we get such data that is not parsed on the application side. Look
forward to reading the article you suggested.
Russ
|||Wow, very cool function. (I'm such a geek for saying that!) Thanks for
the feedback.
Russ