How to Split a String in Oracle
Last updated August 25, 2026 · By the SaturnSQL team
Oracle has no built-in STRING_SPLIT. The standard idiom combines REGEXP_SUBSTR with CONNECT BY LEVEL to turn each delimited token into its own row.
The standard idiom walks the string one token at a time using LEVEL as the occurrence number, stopping once REGEXP_SUBSTR runs out of matches.
SELECT REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) AS color
FROM dual
CONNECT BY REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) IS NOT NULL;Splitting a column, one row per source row
Applying this against a table column needs an extra PRIOR condition that forces Oracle to restart the hierarchical walk for each source row instead of connecting rows to each other, otherwise CONNECT BY treats every matching row as part of one shared hierarchy.
SELECT c.customer_id,
REGEXP_SUBSTR(c.tags, '[^,]+', 1, LEVEL) AS tag
FROM customers c
CONNECT BY REGEXP_SUBSTR(c.tags, '[^,]+', 1, LEVEL) IS NOT NULL
AND PRIOR c.customer_id = c.customer_id
AND PRIOR SYS_GUID() IS NOT NULL;This is a workaround built out of general-purpose functions, not a native split operation, so it materializes the string as an in-memory hierarchical query. It performs fine for short delimited lists but isn't something to run over large columns with dozens of tokens each; for that volume, unpivot the data at load time instead.
Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free