How Can I Efficiently Split Comma-Separated Values in SQLite Using CTEs?

Splitting Comma-Separated Values in SQLite

An alternative approach to working with comma-separated strings in SQLite involves leveraging Common Table Expressions (CTEs). CTEs provide a means to break down the string into individual values effectively.

Step-by-Step Implementation:

1. Create a CTE: Begin by defining a CTE named split that accepts two parameters: word to hold individual values and csv to store the comma-separated string.
2. Initial Query: In the initial query of the CTE, you'll specify the CSV string, concatenating an additional comma to indicate its end.
3. Recursive Query: The recursive query employs substr() and instr() to extract the first word up until the next comma and update the csv to remove the parsed part. This process repeats until no more commas are encountered.
4. Select Result: Finally, select the word column from the CTE, excluding the first and last rows, which represent the raw CSV string and any residual empty text.

Example:

Here's a code sample to illustrate the CTE approach:

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!='';

Output:

Auto
A
1234444

This method offers a concise and efficient way to split comma-separated strings in SQLite, enabling you to access individual values with ease.

The above is the detailed content of How Can I Efficiently Split Comma-Separated Values in SQLite Using CTEs?. For more information, please follow other related articles on the PHP Chinese website!