View Single Post
  #5 (permalink)  
Old May 30th, 2006, 10:58 AM
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

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

Reply With Quote