How to Efficiently Split Comma-Separated Values in SQLite?

Splitting Comma-Separated Values in SQLite

When working with data containing comma-separated values, it can be necessary to extract individual components. In SQLite, using functions like Replace() and Trim() to achieve this can be cumbersome. Here's a more efficient approach using a Common Table Expression (CTE).

Solution:

Step 1: Define the CTE

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: Initializes word and csv columns.
• UNION ALL: Recursively splits csv values by commas.
• WHERE csv != '': Terminates recursion when no more values to split.

Step 2: Extract the Values

SELECT word FROM split WHERE word!='';

• Filters out the first and last rows (dummy/terminal values).
• Extracts the split values into the word column.

Output:

Auto
A
1234444

This solution provides a simple and efficient way to split comma-separated values in SQLite, even when the string lengths vary.

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