How Can I Split Comma-Separated Values in SQLite Using a Common Table Expression (CTE)?

Splitting Comma-Separated Values in SQLite: A Common Table Expression (CTE) Approach

In the realm of data management, the need to split comma-separated values (CSVs) frequently arises. For SQLite, a popular lightweight database system, this task may not be as straightforward as in other SQL environments due to the absence of a dedicated substring() function. However, there is an alternative solution that leverages a Common Table Expression (CTE).

Let's consider the following scenario: we have a table with a "Category" column containing comma-separated strings, and we want to extract individual values from each string into separate rows.

A CTE is a named, temporary result set that can be further referenced and manipulated within a single query. In our case, we can use a CTE to recursively split the CSV strings and store the extracted values in a new table. Here's the query:

WITH split(word, csv) AS (
  -- 'initial query'
  SELECT
    '',
    '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

This query splits the CSV strings using the instr() function to locate the first comma, extracts the substring before the comma, and recursively calls itself to repeat the process on the remaining part of the string. The recursion terminates when there are no more commas left.

The resulting output contains the individual values extracted from the original CSV strings, organized into rows:

Auto
A
1234444

This approach provides a powerful and flexible way to split comma-separated values in SQLite, making it a valuable tool for data manipulation tasks.

The above is the detailed content of How Can I Split Comma-Separated Values in SQLite Using a Common Table Expression (CTE)?. For more information, please follow other related articles on the PHP Chinese website!