sqlindex

Strings

Split a delimited string into rows

3 min · updated June 16, 2026

Split a comma-separated string into one row per part. Warehouses have a function for it; MySQL and SQLite need a workaround.

-- PostgreSQL
SELECT unnest(string_to_array('a,b,c', ',')) AS part;
-- or  SELECT regexp_split_to_table('a,b,c', ',') AS part;

-- DuckDB
SELECT unnest(string_split('a,b,c', ',')) AS part;

-- BigQuery
SELECT part FROM UNNEST(SPLIT('a,b,c', ',')) AS part;

-- Snowflake
SELECT value::string AS part FROM TABLE(SPLIT_TO_TABLE('a,b,c', ','));

MySQL 8+ has no split function — convert the list to a JSON array and use JSON_TABLE:

-- MySQL 8+
SELECT j.part
FROM JSON_TABLE(
  CONCAT('["', REPLACE('a,b,c', ',', '","'), '"]'),
  '$[*]' COLUMNS (part VARCHAR(50) PATH '$')
) AS j;

SQLite has no split either — a recursive CTE walks the string:

-- SQLite
WITH RECURSIVE split(part, rest) AS (
  SELECT '', 'a,b,c' || ','
  UNION ALL
  SELECT substr(rest, 1, instr(rest, ',') - 1),
         substr(rest, instr(rest, ',') + 1)
  FROM split
  WHERE rest <> ''
)
SELECT part FROM split WHERE part <> '';

The JSON-array trick breaks on quotes/commas in values. REPLACE(...) to build a JSON array assumes the parts contain no " or ,. For messy data, store a real JSON array column and use the array-to-rows recipe instead, or split in your application layer.

← All recipes