STRING SPLIT / STRING TO ROWSFunctions
Buy Me A Coffee

Split a delimited string into multiple rows or elements. Commonly needed for storing comma-separated lists in a single column and querying them relationally.

Splits a delimited string into individual rows, available as STRING_SPLIT (SQL Server), unnest with string_to_array (PostgreSQL), and similar functions. Essential for parsing comma-separated values and handling many-to-many relationships stored as delimited strings.

Compatibility

Show:
Database System Support Status Since Version Notes
MySQL ✗ Not Supported — MySQL has no built-in string split function that returns rows. Common workarounds: (1) a recursive CTE (MySQL 8.0+) to iterate through the string; (2) a numbers/tally table approach; (3) JSON_TABLE with JSON_ARRAY conversion. The cleanest approach in MySQL 8.0+ is a recursive CTE.
PostgreSQL ✓ Supported 8.0 string_to_array(str, delimiter) converts a delimited string to a PostgreSQL array, then unnest() expands the array into rows. regexp_split_to_table(str, pattern) splits by regex and directly returns a set of rows. string_to_table(str, delimiter) (PostgreSQL 14+) is the most direct single-function approach.
SQL Server ✓ Supported 2016 STRING_SPLIT(string, separator) returns a single-column table (value) of split elements. SQL Server 2022 added an optional third argument (enable_ordinal) that, when set to 1, adds an ordinal column preserving element order. Prior to 2016, the common workarounds were XML-based splitting or a numbers-table TVF.
Oracle ✓ Supported 10g REGEXP_SUBSTR with CONNECT BY LEVEL is the classic Oracle approach (10g+). Oracle 12c added the ability to use LATERAL with XMLTABLE for cleaner syntax. The built-in APEX_STRING.SPLIT function (if Oracle APEX is installed) provides a simple table function. There is no single built-in function equivalent to STRING_SPLIT.
SQLite ✗ Not Supported — SQLite has no string split function. The json_each() workaround (converting the CSV to a JSON array) is the most practical approach in SQLite 3.9+. A recursive CTE can also parse a delimited string.

Details

The underlying need (querying a delimited column relationally) is a sign of a denormalized schema — the real fix is normalizing into a child table. That said, STRING_SPLIT (SQL Server 2016+), unnest(string_to_array()) (PostgreSQL), and the json_each workaround (SQLite/MySQL) are commonly needed for migrating or querying legacy data. SQL Server 2016's STRING_SPLIT does not guarantee element order — the ordinal column only arrives in SQL Server 2022. Oracle has no single built-in; the CONNECT BY LEVEL + REGEXP_SUBSTR pattern is the de facto standard but breaks on multi-row inputs without careful PRIOR + SYS_GUID() fencing.

Standard Syntax

-- SQL Server 2016+: SELECT value FROM STRING_SPLIT('a,b,c,d', ','); -- PostgreSQL: SELECT unnest(string_to_array('a,b,c,d', ',')) AS value;

Version Support

MySQL: Not supported PostgreSQL: Since 8.0 SQL Server: Since 2016 Oracle: Since 10g SQLite: Not supported

Per-Database Syntax & Notes

MySQL

MySQL has no built-in string split function that returns rows. Common workarounds: (1) a recursive CTE (MySQL 8.0+) to iterate through the string; (2) a numbers/tally table approach; (3) JSON_TABLE with JSON_ARRAY conversion. The cleanest approach in MySQL 8.0+ is a recursive CTE.

-- Recursive CTE workaround (MySQL 8.0+): WITH RECURSIVE splitter AS ( SELECT SUBSTRING_INDEX('a,b,c,d', ',', 1) AS val, SUBSTRING('a,b,c,d', LOCATE(',','a,b,c,d')+1) AS rest, 1 AS lev UNION ALL SELECT SUBSTRING_INDEX(rest, ',', 1), IF(LOCATE(',', rest) > 0, SUBSTRING(rest, LOCATE(',', rest)+1), ''), lev + 1 FROM splitter WHERE rest <> '' ) SELECT val FROM splitter; -- JSON_TABLE workaround (MySQL 8.0+): SELECT jt.val FROM JSON_TABLE( CONCAT('["', REPLACE('a,b,c,d', ',', '","'), '"]'), '$[*]' COLUMNS (val VARCHAR(100) PATH '$') ) jt;

PostgreSQL

string_to_array(str, delimiter) converts a delimited string to a PostgreSQL array, then unnest() expands the array into rows. regexp_split_to_table(str, pattern) splits by regex and directly returns a set of rows. string_to_table(str, delimiter) (PostgreSQL 14+) is the most direct single-function approach.

-- unnest + string_to_array (most common): SELECT unnest(string_to_array('a,b,c,d', ',')) AS val; -- regexp_split_to_table (regex delimiter): SELECT regexp_split_to_table('a, b, c , d', '\s*,\s*') AS val; -- string_to_table (PostgreSQL 14+, cleanest): SELECT * FROM string_to_table('a,b,c,d', ',') AS val; -- Use in a JOIN against a table column: SELECT p.id, tag.val FROM products p, unnest(string_to_array(p.tags, ',')) AS tag(val) WHERE tag.val = 'featured';

SQL Server

STRING_SPLIT(string, separator) returns a single-column table (value) of split elements. SQL Server 2022 added an optional third argument (enable_ordinal) that, when set to 1, adds an ordinal column preserving element order. Prior to 2016, the common workarounds were XML-based splitting or a numbers-table TVF.

-- Basic split (2016+): SELECT value FROM STRING_SPLIT('a,b,c,d', ','); -- With ordinal column (2022+): SELECT ordinal, value FROM STRING_SPLIT('a,b,c,d', ',', 1) ORDER BY ordinal; -- JOIN against a table column: SELECT p.id, s.value AS tag FROM products p CROSS APPLY STRING_SPLIT(p.tags, ',') s WHERE s.value = 'featured'; -- Pre-2016 XML workaround: SELECT t.c.value('.', 'VARCHAR(100)') AS val FROM ( SELECT CAST('<x>' + REPLACE('a,b,c,d',',','</x><x>') + '</x>' AS XML) AS x ) doc CROSS APPLY doc.x.nodes('/x') t(c);

Oracle

REGEXP_SUBSTR with CONNECT BY LEVEL is the classic Oracle approach (10g+). Oracle 12c added the ability to use LATERAL with XMLTABLE for cleaner syntax. The built-in APEX_STRING.SPLIT function (if Oracle APEX is installed) provides a simple table function. There is no single built-in function equivalent to STRING_SPLIT.

-- REGEXP_SUBSTR + CONNECT BY (most common pattern): SELECT REGEXP_SUBSTR('a,b,c,d', '[^,]+', 1, LEVEL) AS val FROM DUAL CONNECT BY LEVEL <= REGEXP_COUNT('a,b,c,d', ',') + 1; -- Against a table column: SELECT p.id, REGEXP_SUBSTR(p.tags, '[^,]+', 1, LEVEL) AS tag FROM products p CONNECT BY LEVEL <= REGEXP_COUNT(p.tags, ',') + 1 AND PRIOR p.id = p.id AND PRIOR SYS_GUID() IS NOT NULL;

SQLite

SQLite has no string split function. The json_each() workaround (converting the CSV to a JSON array) is the most practical approach in SQLite 3.9+. A recursive CTE can also parse a delimited string.

-- json_each workaround (3.9+): SELECT j.value FROM json_each( '["' || REPLACE('a,b,c,d', ',', '","') || '"]' ); -- Recursive CTE workaround: WITH RECURSIVE split(val, rest) AS ( SELECT SUBSTR('a,b,c,d', 1, INSTR('a,b,c,d', ',') - 1), SUBSTR('a,b,c,d', INSTR('a,b,c,d', ',') + 1) UNION ALL SELECT CASE WHEN INSTR(rest, ',') > 0 THEN SUBSTR(rest, 1, INSTR(rest, ',') - 1) ELSE rest END, CASE WHEN INSTR(rest, ',') > 0 THEN SUBSTR(rest, INSTR(rest, ',') + 1) ELSE '' END FROM split WHERE rest != '' ) SELECT val FROM split;