Parse a character seperated list of items

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

.

Relevant Pages