Splitting on commas in SQLite

I had an input string in x,y,z format and I needed to split it into three separate values in SQLite. I managed to do it using a confusing combination of the instr() and substr() functions.

Here's what I came up with:

with comma_locations as (
  select instr(:path, ',') as first_comma,
  instr(:path, ',') + instr(substr(:path, instr(:path, ',') + 1), ',') as second_comma
), variables as (
  select
    substr(:path, 0, first_comma) as first,
    substr(:path, first_comma + 1, second_comma - first_comma - 1) as second,
    substr(:path, second_comma + 1) as third
  from comma_locations
)
select * from variables

Against an input of x12,y1234,z12345 it returns this:

first	second	third
x12	y1234	z12345

Here's a live demo of the query.

Created 2021-02-01T11:02:59-08:00 · Edit

Splitting on commas in SQLite

Related