Split csv entries from a column in a table

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.

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'

;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

Dennes Torres 23 April 2024

4 Reasons to use KustoDB for Real-Time

When implementing real-time ingestion, we usually implement an architecture called lambda. Using the lambda architecture, KustoDB in Microsoft Fabric is always recommended for the speed layer. Do you know why? Let’s analyze in detail. 1 – KustoDB uses SSD KustoDB uses an internal SSD storage. Lakehouses use ADLS as their backend. In this way, Kusto … Read more

Split csv entries from a column in a table

Subscribe for more articles

Rate this article

Pop Rivett

Related articles

4 Reasons to use KustoDB for Real-Time