Splitting Comma-Separated Values with SQLite's Common Table Expressions

Question:

How can I effortlessly split a comma-separated string in the Category column of a SQLite table? I seek a simpler approach than using Replace() and Trim() and avoid the limitations of substr().

Answer:

SQLite offers a feature called Common Table Expressions (CTEs) that allows for recursive queries, making it convenient to split comma-separated values. Here's a breakdown:

Query:

WITH split(word, csv) AS (
  SELECT '', 'Auto,A,1234444'||','
  UNION ALL
  SELECT substr(csv, 0, instr(csv, ',')), substr(csv, instr(csv, ',')   1)
  FROM split
  WHERE csv != ''
)
SELECT word FROM split
WHERE word!='';

Explanation:

• 'initial query': Initializes the CTE with a single row containing an empty string and the comma-separated string terminated with an extra comma.
• 'recursive query': Recursively selects words up to the next comma and the remaining portion of the string, continuously splitting it until no more commas are found.
• 'final query': Filters out the initial empty row and terminal comma from the split results.

Output:

Auto
A
1234444