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:

Here's a live demo of the query.

Related

• sqlite Combining substr and instr to extract text - 2022-02-15
• sqlite Ordered group_concat() in SQLite - 2022-02-06
• datasette Searching all columns of a table in Datasette - 2021-08-23
• sqlite Combining CTEs and VALUES in SQLite - 2023-01-29
• sqlite Identifying column combination patterns in a SQLite table - 2021-01-12
• sqlite SQLite aggregate filter clauses - 2021-08-04
• sqlite List all columns in a SQLite database - 2020-05-06
• postgresql Using unnest() to use a comma-separated string as the input to an IN query - 2021-04-10
• bash Looping over comma-separated values in Bash - 2020-09-01
• postgresql Using json_extract_path in PostgreSQL - 2021-04-13

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