This is all in a single query without having to create a function. The separator is in a cte so doesn’t need to be declared separately. For longer separators include the length in this cte and use it for the length adjustments in the query.
|
1 2 3 4 |
create table #a (rowid int, csvstring varchar(100)) insert #a select 1, 'a,b,c,asd,yyyy' insert #a select 2, 'x' insert #a select 3, 'fhasgdff,wretyr' |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
;with csv as (select s=',') , cte1 as ( select rowid=rowid, csvstring = csvstring + csv.s, strtloc = 1, endloc = charindex(csv.s,csvstring+csv.s)-1 from #a,csv where len(csvstring) > 0 union all select rowid, csvstring, strtloc = endloc+2, endloc = charindex(csv.s,csvstring,endloc+2)-1 from cte1,csv where charindex(csv.s,csvstring,endloc+2) > 0 ) , cte as ( select rowid, str=substring(csvstring,strtloc, endloc-strtloc+1), strtloc from cte1 ) select rowid, str from cte order by rowid, strtloc |
Load comments