p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   SQL Server 2000 (http://p2p.wrox.com/forumdisplay.php?f=20)
-   -   Having trouble splitting a list to a resultset? (http://p2p.wrox.com/showthread.php?t=43180)

Peso May 29th, 2006 07:34 PM

Having trouble splitting a list to a resultset?
 
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

Raghavendra_Mudugal May 30th, 2006 12:26 AM

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

Peso May 30th, 2006 02:36 AM

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

rstelma May 30th, 2006 10:36 AM

Peso,

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

Richard


Peso May 30th, 2006 10:58 AM

Quote:

quote:Originally posted by rstelma
 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



All times are GMT -4. The time now is 11:40 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.