How to split comma-separated values?
sql, sqlite
Solution
You can use a Common Table Expression to split comma separated values in SQLite.
WITH split(word, csv) AS (
-- 'initial query' (see SQLite docs linked above)
SELECT
-- in final WHERE, we filter raw csv (1st row) and terminal ',' (last row)
'',
-- here you can SELECT FROM e.g. another table: col_name||',' FROM X
'Auto,A,1234444'||',' -- terminate with ',' indicating csv ending
-- 'recursive query'
UNION ALL SELECT
substr(csv, 0, instr(csv, ',')), -- each word contains text up to next ','
substr(csv, instr(csv, ',') + 1) -- next recursion parses csv after this ','
FROM split -- recurse
WHERE csv != '' -- break recursion once no more csv words exist
) SELECT word FROM split
WHERE word!=''; -- filter out 1st/last rows
Output is as expected:
Auto
A
1234444
Problem
I want to split a comma-separated string in `Category` column : Category Auto,A,1234444 Auto,B,2345444 Electronincs,Computer,33443434 I want to get only a single value from above string: ``` value1: Auto value2: A value3: 1234444 ``` I found how using `Replace()` and `Trim()`. However, I want an easier approach. In SQL there is `SubString()` but not in SQLite. `substr()` can set a maximum length but my string doesn't have fixed length.