How To Split Comma-separated Value In Sqlite?

I want to split comma-separated String inside SQLite database Example: I have a Category column in 1 of my table. |Category | |------------------------------|

Solution 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 categoriesSELECT'', 'Auto,A,1234444'||','UNIONALLSELECT
    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
A1234444

Solution 2:

SQLite provide functions for this purpose, e.g. to get substring substr('your string', start_position, end_position), to get position of a specific character in a string instr('22:string', ':'), and to get length of a string length('string'). Now let see the following examples:

selectsubstr('22:khan', x, y);
returns a string starting at xand ends with y;
selectsubstr('22:khan', 0, instr('22:khan',':'));
returns: 22selectsubstr('22:khan', instr('22:khan',':')+1, length('22:khan'));
returns: khan
selectsubstr('22:khan',instr('22:khan',':'), length('22:khan'));
returns: :khan
selectsubstr('Noor,Khan', 0, instr('Noor,Khan', ','));
returns: Noor
selectsubstr('Noor,Khan', instr('Noor,Khan', ',')+1, length('Noor,Khan'));
returns: Khan

for more info visit: https://www.sqlite.org/lang_corefunc.html

Solution 3:

I like the answer from @user1461607 except: it seems to me the SQLite documentation warns against assuming any particular order from a SELECT, both in the general case, and in the specific case of a recursive SELECT. Here, I modified that answer to add an ordering column in a manner that I think SQLite guarantees to work.

I also cosmetically changed the example from a comma-separated list to a path, to suggest there are cases where you really need to process things in a particular order. This example also prints out all the columns from the temporary table so it's slightly easier to see what went on. AFAICT, a CTE in SQLite does not have the usual ROWID column, so it seems like adding some ordering column yourself really is required to sleep soundly at night.

WITHRECURSIVE split(seq, word, str) AS (
    SELECT0, '/', 'home/ronburk/layers/branch'||'/'UNIONALLSELECT
        seq+1,
        substr(str, 0, instr(str, '/')),
        substr(str, instr(str, '/')+1)
    FROM split WHERE str !=''
) SELECT*FROM split ORDERBY split.seq ASC;

Solution 4:

This variation of the answer provided by @user1461607 ensures that any CSV values that happen to be empty strings are preserved:

WITHRECURSIVE split(value, str) AS (
      SELECTnull, 'Auto,A,1234444'||','-- the string to be split UNIONALLSELECT
      substr(str, 0, instr(str, ',')),
      substr(str, instr(str, ',')+1)
      FROM split WHERE str!=''
  ) SELECTvalueFROM split WHEREvalueisnotNULL;

Converting a CSV line to a JSON array

Assuming the JSON1 extension has been loaded, you could use json_group_array(value) in the last line to convert the CSV to a JSON array of strings.