How to split comma-separated values?

Question

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.

Answer 1

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

Answer 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:

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

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

Answer 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.

WITH RECURSIVE split(seq, word, str) AS (
    SELECT 0, '/', 'home/ronburk/layers/branch'||'/'
    UNION ALL SELECT
        seq+1,
        substr(str, 0, instr(str, '/')),
        substr(str, instr(str, '/')+1)
    FROM split WHERE str != ''
) SELECT * FROM split ORDER BY split.seq ASC;

Answer 4

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

WITH RECURSIVE split(value, str) AS (
      SELECT null, 'Auto,A,1234444' || ','  -- the string to be split 
      UNION ALL
      SELECT
      substr(str, 0, instr(str, ',')),
      substr(str, instr(str, ',')+1)
      FROM split WHERE str!=''
  ) SELECT value FROM split WHERE value is not NULL;

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.

Answer 5

Late to the party, but I found an extension which replicates the behaviour of the SPLIT_PART function of PostgreSQL

SELECT split_part(category, ',', 3) FROM ...

https://github.com/nalgeon/sqlean/blob/main/docs/text.md#split_partsource-sep-part