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.ha***@gmail.com) writes:
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.

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

On 20 Mar 2006 12:07:45 -0800, russ.ha***@gmail.com wrote:

Hi Russ,

http://www.sommarskog.se/arrays-in-sql.html

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
---

Wow, very cool function. (I'm such a geek for saying that!) Thanks for
the feedback.

Russ

The Data Miner: SQL Server Data Mining Newsletter (March 2006)
SQL Server Management Studio error on connection
New Query with Current Connection
Memory allocation in MS-SQL 2000 instances
SQL Management Studio 2005
SS05 Profiler questions
Auto-refresh the output of the 'Open Table" action
Move Database Store
Hidden max number of columns in Management Studio?
how do I view a database transaction log?