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:

1
with comma_locations as (
2
  select instr(:path, ',') as first_comma,
3
  instr(:path, ',') + instr(substr(:path, instr(:path, ',') + 1), ',') as second_comma
4
), variables as (
5
  select
6
    substr(:path, 0, first_comma) as first,
7
    substr(:path, first_comma + 1, second_comma - first_comma - 1) as second,
8
    substr(:path, second_comma + 1) as third
9
  from comma_locations
10
)
11
select * from variables