Split in Oracle SQL: Turn Delimited Text Into Rows

How-to basics · Updated November 22, 2026

Oracle SQL does not provide a general-purpose SPLIT() function for turning delimited text into rows. A common solution is REGEXP_SUBSTR with CONNECT BY; use INSTR and SUBSTR when you need more control or want to avoid regular expressions.

What “split” means in Oracle SQL

Splitting can describe two different operations. You might divide one string into multiple rows, such as converting red,green,blue into three records, or extract several fields into separate columns. The right query depends on which result you need, how the delimiter behaves, and whether empty fields matter.

Oracle has string functions such as SUBSTR and INSTR, and regular-expression functions such as REGEXP_SUBSTR. These are building blocks rather than a universal SQL SPLIT function. Examples below assume a table called source_table with an identifier column, id, and a text column, csv_text.

Split a comma-delimited value into rows

For simple, non-empty comma-separated tokens, REGEXP_SUBSTR can return the first token, then the second, and so on. In Oracle 12c and later, a correlated CROSS APPLY makes it possible to generate the token rows for each source row:

SELECT s.id,
       x.token_no,
       x.token
FROM source_table s
CROSS APPLY (
  SELECT LEVEL AS token_no,
         REGEXP_SUBSTR(s.csv_text, '[^,]+', 1, LEVEL) AS token
  FROM dual
  CONNECT BY REGEXP_SUBSTR(s.csv_text, '[^,]+', 1, LEVEL) IS NOT NULL
) x
ORDER BY s.id, x.token_no;

The pattern [^,]+ means “one or more characters that are not commas.” The first call returns the first match, the second call returns the second match, and so on. The hierarchical query stops when there is no next match. For example, red,green,blue produces three rows with token numbers 1, 2, and 3.

This pattern deliberately skips empty fields. For red,,blue, it returns red and blue, not a middle row for the empty value. It also returns no token rows for a null string. Those details are important: the short query is suitable only when skipping empty fields is acceptable.

Preserve empty fields between delimiters

If a comma can represent a missing field, use a pattern that matches each comma and captures what precedes it. Appending a comma gives the final token a delimiter too, including when the original value ends in a comma. The query below generates one row per field, including fields whose value is empty:

SELECT s.id,
       x.token_no,
       REGEXP_SUBSTR(s.csv_text || ',', '([^,]*)(,)', 1, x.token_no, NULL, 1) AS token
FROM source_table s
CROSS APPLY (
  SELECT LEVEL AS token_no
  FROM dual
  CONNECT BY LEVEL <= LENGTH(s.csv_text)
                         - LENGTH(REPLACE(s.csv_text, ',', '')) + 1
) x
WHERE s.csv_text IS NOT NULL
ORDER BY s.id, x.token_no;

The count expression calculates the number of commas and adds one. The regular expression captures zero or more non-comma characters before each comma; the final argument, 1, asks REGEXP_SUBSTR to return the first captured group rather than the full match. An empty field is represented by Oracle as NULL, but its row is still present. Thus red,,blue, produces four rows: red, an empty value, blue, and an empty value.

The WHERE condition excludes null input. Oracle treats a zero-length character string as null, so an empty input cannot be distinguished from null in the usual VARCHAR2 column. Decide separately whether a null source value should produce zero rows or a row with a null token; that is a data rule, not just a string-function choice.

Extract delimited values into columns

When the number and order of fields are fixed, call REGEXP_SUBSTR once for each position instead of generating rows. For example, with first_name,last_name,department stored in csv_text, the first, second, and third occurrences can be selected as separate columns:

SELECT id,
       REGEXP_SUBSTR(csv_text, '[^,]+', 1, 1) AS field_1,
       REGEXP_SUBSTR(csv_text, '[^,]+', 1, 2) AS field_2,
       REGEXP_SUBSTR(csv_text, '[^,]+', 1, 3) AS field_3
FROM source_table;

This compact version has the same limitation as the basic row splitter: it skips empty fields, so a missing middle value can shift the later values to earlier positions. If the fields have a known structure, consider storing them in separate columns or parsing with logic that accounts for missing values. A delimited string is not a reliable substitute for a properly structured record when fields can be absent or ambiguous.

How the Oracle string functions work

REGEXP_SUBSTR returns a substring matching a regular-expression pattern. Its arguments include the source string, pattern, starting character position, and occurrence number. In the examples, position 1 means start at the beginning, while LEVEL or token_no selects the match to return. The pattern is a regular expression, not an ordinary literal string: characters such as a period, bracket, or asterisk may have special meaning.

INSTR finds the position of a character or substring, and SUBSTR extracts characters from a position. Together they can split strings without regular expressions, which may be useful for performance-sensitive work or for handling a fixed delimiter precisely. That approach requires careful position and length calculations, particularly for leading, repeated, or trailing delimiters. Test those cases rather than assuming a query handles them.

The comma examples treat a comma as a separator, not as part of a quoted value. A true CSV field can contain quoted commas, escaped quotes, or line breaks. For example, "Seattle, WA" is one CSV field despite the comma inside it. The simple regular expressions shown here will split it incorrectly. Use a proper CSV-aware loading or parsing process when the input follows CSV quoting rules.

Choose the right method for the input

Before selecting a query, establish what the delimiter and tokens mean. A quick decision guide is:

  • Simple separator and empty fields do not matter: use REGEXP_SUBSTR with a pattern such as [^,]+.
  • Empty fields must keep their positions: count delimiters and extract each field, or use a tested parser that preserves empty tokens.
  • A fixed number of output fields: request each occurrence as a separate column.
  • Quoted CSV or complex escaping: use a CSV-aware import or parser, not a basic delimiter pattern.
  • Repeated processing of important data: consider normalizing the data into related rows instead of splitting it each time a query runs.

Oracle also has options that may fit a particular environment. APEX_STRING.SPLIT is available in Oracle Application Express installations and can return split values as a collection; it is not a built-in function guaranteed to exist in every Oracle database. DBMS_UTILITY.COMMA_TO_TABLE is intended for comma-separated Oracle names, not arbitrary user text, and should not be treated as a general-purpose string splitter. Check availability, supported input, and version documentation before relying on either option.

Common problems and performance considerations

A frequent error is using [^,]+ and expecting it to preserve blank fields. The plus sign means one or more characters, so an empty value cannot match. Changing it casually to an asterisk is not enough: a pattern that can match zero characters needs careful handling to avoid empty-match and occurrence-position problems. The delimiter-consuming pattern and field-count approach above makes the intended behavior explicit.

Another issue is losing the original order. SQL results have no guaranteed order unless the query includes an ORDER BY. Keep a token number and sort by the source identifier and token number whenever downstream logic depends on sequence. If identifiers are not unique, include a key that uniquely identifies each source row.

Regular expressions are expressive, but their extra flexibility has a cost. If a query splits many long values across a large table, benchmark it with representative data. Avoid splitting the same column repeatedly in separate expressions when a single parsing step or a normalized table can serve the query better. Also check maximum string lengths, character sets, and whether the delimiter can occur inside a token. A query that works for a short sample may not cover those production cases.

Most importantly, splitting at query time does not validate the content. It does not establish that a token is a valid number, date, identifier, or permitted value. Validate and convert tokens explicitly, and decide how malformed rows should be reported rather than letting an accidental conversion error determine the outcome.

Frequently Asked Questions

Does Oracle SQL have a SPLIT function?
No general-purpose built-in SQL function named SPLIT is available for arbitrary delimited text. Common approaches use REGEXP_SUBSTR, or combine INSTR and SUBSTR. Some installed products, such as Oracle APEX, provide their own splitting utilities.

How do I split a string into rows?
For simple values where empty fields can be skipped, use REGEXP_SUBSTR with CONNECT BY to request successive matches. On Oracle 12c and later, a correlated CROSS APPLY is a convenient way to run that token-generating query for each source row.

How do I keep empty values between commas?
Do not use the [^,]+ pattern, because it matches only non-empty tokens. Generate a row for every field position and extract the captured value for that position. In Oracle, an empty extracted string appears as NULL, even though its token row can still be retained.

Can a regular expression split quoted CSV correctly?
Not with the simple patterns in this article. Quoted CSV allows separators and line breaks inside fields and uses escaping rules. Use a parser or import method that understands those rules instead of splitting at every comma.

Should I store comma-separated lists in a database column?
It can be acceptable for display text or an external format, but it is often awkward when individual items must be searched, validated, joined, or updated. A related table with one row per item generally makes those database operations clearer and more reliable.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.