Selecting comma separated data as multiple rows with SQLite

A while back I needed to split data stored in one column as a comma separated string into multiple rows in a SQL query from a SQLite database.

My table looked like this:

CREATE TABLE "predictor_sets" 
  (`id` INTEGER, `nvar` INTEGER, `predictors` TEXT, 
    `experiment` TEXT, PRIMARY KEY(`id`));

Insert some sample data:

INSERT INTO predictor_sets VALUES
  (1659, 5, 'BO_bathymax,BO_calcite,BO_parmax,BO_damean,BO_sstrange', 'bathymetry'),
  (1660, 5, 'BO_bathymin,BO_calcite,BO_parmax,BO_damean,BO_sstrange', 'bathymetry'),
  (1661, 5, 'BO_bathymean,BO_calcite,BO_parmax,BO_damean,BO_sstrange', 'bathymetry');

Splitting up the different predictors in seperate rows can be done with the following recursive common table expression:


WITH RECURSIVE split(predictorset_id, predictor_name, rest) AS (
  SELECT id, '', predictors || ',' FROM predictor_sets WHERE id
   UNION ALL
  SELECT predictorset_id, 
         substr(rest, 0, instr(rest, ',')),
         substr(rest, instr(rest, ',')+1)
    FROM split
   WHERE rest <> '')
SELECT predictorset_id, predictor_name 
  FROM split 
 WHERE predictor_name <> ''
 ORDER BY predictorset_id, predictor_name;

Check out the documentation for more info on writing your own common table expressions in SQLite, PostgreSQL or your favorite database.

1 comment:

Alex said...

Thanks for the code! I parsed my 'note' string correctly (|-delimited with a : starting the sub_string) and the results returned into a stacked column. Any suggestions to put each sub_string into its own column?

WITH RECURSIVE split(a_id, sub_string, rest) AS (
SELECT id, '', note || '|'
FROM activities
WHERE subject LIKE '%Q1-2018%'
UNION ALL
SELECT a_id,
substr(rest, instr(rest, ':')+1, instr(rest, '|')-instr(rest, ':')-1),
substr(rest, instr(rest, '|')+1)
FROM split
WHERE rest <> '')
SELECT a_id, sub_string
FROM split
WHERE sub_string <> ''
ORDER BY a_id;