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);