View Single Post
  #1 (permalink)  
Old May 29th, 2006, 07:34 PM
Peso Peso is offline
Friend of Wrox
 
Join Date: May 2006
Location: Helsingborg, , Sweden.
Posts: 246
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
Reply With Quote