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