How to split comma-separated value in SQLite?

Question

I want to split comma-separated String inside SQLite database

Example: I have a Category column in 1 of my table.

|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 have searched a lot on Google; I found a way to replace comma using Replace() and Trim(). However, I want to get an easier approach.

In SQL, there is provide SubString(). But in SQLite, there is no such function. How to solve it?

EDIT: I have checked substr(). But, this function can be set maximum length to get the string value, and my string value doesn't have fixed length.

Answer 1

You can use a common table expression to split comma separated values in SQLite.

WITH split(word, str) AS (
    -- alternatively put your query here
    -- SELECT '', category||',' FROM categories
    SELECT '', 'Auto,A,1234444'||','
    UNION ALL SELECT
    substr(str, 0, instr(str, ',')),
    substr(str, instr(str, ',')+1)
    FROM split WHERE str!=''
) SELECT word FROM split WHERE word!='';

Output is as expected:

Auto
A
1234444