Ok, run this...

SELECT * FROM dbo.fnListSplit('a,10,john.doe#whitehouse.gov,john doe,green,president', ',')

-------------------------------------------------------------
CREATE FUNCTION dbo.fnListSplit
(
    @List VARCHAR(8000),
    @Delimiter VARCHAR(1)
)
RETURNS @Resultset TABLE (i SMALLINT IDENTITY(0, 1), x VARCHAR(50))
AS

BEGIN
    INSERT @Resultset
            (
                x
            )
    SELECT SUBSTRING(@Delimiter + @List + @Delimiter, w.i + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i + 1) - w.i - 1)
    FROM (
                SELECT v0.n + v1.n i
                FROM (
                        SELECT 0 n UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
                    )v0,
                    (
                        SELECT 0 n UNION ALL SELECT 16 UNION ALL SELECT 32 UNION ALL SELECT 48 UNION ALL SELECT 64 UNION ALL SELECT 80 UNION ALL SELECT 96 UNION ALL SELECT 112 UNION SELECT 128 UNION ALL SELECT 144 UNION ALL SELECT 160 UNION ALL SELECT 176 UNION ALL SELECT 192 UNION ALL SELECT 208 UNION ALL SELECT 224 UNION ALL SELECT 240
                    ) v1
            ) w
    WHERE w.i = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i) AND w.i < LEN(@Delimiter + @List)
    ORDER BY w.i

    RETURN
END

DONE.

I am getting the output as
---------
i | x
---------
0 a
1 b
2 c
3 d
4 e
5 f
--------
But, I did'nt get your problem, what exactly you are looking for.

hope to see information from you.


With Regards,
Raghavendra Mudugal

I wasn't looking for anything. Just helping out a lot of programmers with a problem that is often recurring.

Peso,

Thank you for this and other posts. Sharing info is most appreciated. This function will split up to 26 items???

Richard

Yes, the function above will split at most 256 values. If you don't need so many values, here is another example that allows up to 16 splits.

CREATE FUNCTION dbo.fnListSplit
(
    @List VARCHAR(850),
    @Delimiter VARCHAR(1)
)
RETURNS @Resultset TABLE (i SMALLINT IDENTITY(0, 1), x VARCHAR(50))
AS

BEGIN
    INSERT @Resultset
            (
                x
            )
    SELECT SUBSTRING(@Delimiter + @List + @Delimiter, w.i + 1, CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i + 1) - w.i - 1)
    FROM (
                       SELECT 0 i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15
            ) w
    WHERE w.i = CHARINDEX(@Delimiter, @Delimiter + @List + @Delimiter, w.i) AND w.i < LEN(@Delimiter + @List)
    ORDER BY w.i

    RETURN
END

For example, say you want the third and fourth value in the list.

Just write

SELECT x FROM dbo.fnListSplit('a,b,c,d,e,f', ',') where i = 3 or i = 4