http://visualstudiogallery.msdn.microsoft.com/8e2103b6-87cf-4fef-9410-a580c434b602

DECLARE @str varchar(8000)

SET @str = '10,2,3,4,5,6,7,8,9'


DECLARE @InputString varchar(8000)

SELECT @InputString = ',' + @str + ','


;with qry(n, names) as

       (select len(list.names) - len(replace(list.names, ',', '')) - 1 as n, substring(list.names, 2, len(list.names)) as names

        from (select @InputString names) as list

        union all

        select (n - 1) as n,

               substring(names, 1 + charindex(',', names), len(names)) as names

        from qry

        where n > 1)

 select substring(names, 1, charindex(',', names) - 1) dwarf

 from qry;





DECLARE @str varchar(8000)

SET @str = '10,2,3,4,5,6,7,8,9'


DECLARE @InputString varchar(8000)

SELECT @InputString = @str + ','


;WITH RecursiveCSV(x,y) 

AS 

(

    SELECT 

        x = SUBSTRING(@InputString,0,CHARINDEX(',',@InputString,0)),

        y = SUBSTRING(@InputString,CHARINDEX(',',@InputString,0)+1,LEN(@InputString))

    UNION ALL

    SELECT 

        x = SUBSTRING(y,0,CHARINDEX(',',y,0)),

        y = SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y))

    FROM 

        RecursiveCSV 

    WHERE

        SUBSTRING(y,CHARINDEX(',',y,0)+1,LEN(y)) <> '' OR 

        SUBSTRING(y,0,CHARINDEX(',',y,0)) <> ''

)

(select x FROM RecursiveCSV)

OPTION (MAXRECURSION 32767);

+ Recent posts