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_inde
x 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_inde
x AS VARCHAR)
END


IF(@item_count = @index_number - 1)
BEGIN

SET @return_item =  SUBSTRING(@string_li
st,
@start_position, (LEN(@string_list) - @start_position) + 1)


END
ELSE
BEGIN
IF(@item_count = @index_number)
BEGIN
SET @return_item =  SUBSTRING(@string_li
st,
@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_mes
sage,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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/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