Split Function equivalent in tsql using CTE approach

Posted by Niladri.Biswas under Sql Server category on | Points: 40 | Views : 795
I'm looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a table or table variable.

Does anyone have a function that returns each one in a row?

DECLARE @str varchar(100),@delimiter varchar(10)
SET @str= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'
;with cte as
select 0 a, 1 b
union all
select b, charindex(',', @str, b) + len(',')
from cte
where b > a
select substring(@str,a,
case when b > len(',') then b-a-len(',') else len(@str) - a + 1 end) value
from cte where a >0

Comments or Responses

Login to post response