Oracle SQL String Split: Methods and Examples

How-to basics · Updated November 22, 2026

Oracle SQL does not provide a general-purpose SPLIT function that turns any delimited string into rows. For a simple delimiter, a common SQL approach is REGEXP_SUBSTR with a hierarchical query; it works well for ordinary nonempty tokens, but it does not preserve empty fields.

What string splitting means in Oracle

Splitting a string means identifying separators in a value and returning the pieces around them. For example, splitting red,green,blue on commas produces three tokens: red, green, and blue. In Oracle, you can do this in a SQL query with regular-expression functions, a row generator, or a purpose-built function. The right choice depends on whether the input is one value or a column, whether empty tokens matter, and which Oracle features are available.

It is useful to distinguish splitting into rows from splitting into columns. Rows are usually more flexible because the number of tokens can vary; a query can return one row per token. Turning tokens into a fixed set of columns requires knowing how many columns to return and extracting each position separately.

Split one value with REGEXP_SUBSTR

For a short, comma-separated value with no empty fields, use a regular expression that matches one or more characters other than a comma. Oracle’s fourth REGEXP_SUBSTR argument selects the match occurrence, and LEVEL supplies occurrences numbered from one upward.

SELECT REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) AS token
FROM dual
CONNECT BY REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) IS NOT NULL;

The result is three rows, in order: red, green, and blue. In the pattern, [^,]+ means one or more characters that are not commas. The first argument after the pattern is the starting position, and the next is the occurrence number. The hierarchical query keeps generating rows until the expression no longer finds a match.

This pattern treats the delimiter as a boundary, not as part of a token. For a pipe-delimited value, for example, use [^|]+. A delimiter such as a period is also literal inside this particular character class. More complicated regular-expression patterns may require escaping special characters, so check how the delimiter is represented before adapting a pattern.

Split a column into rows

For a table, generate the occurrence numbers for each source row and extract the corresponding token. On Oracle versions that support the CROSS APPLY clause, this form makes the row generator depend on the current table row:

SELECT t.id,
       REGEXP_SUBSTR(t.csv, '[^,]+', 1, seq.n) AS token
FROM items t
CROSS APPLY (
  SELECT LEVEL AS n
  FROM dual
  CONNECT BY LEVEL <= REGEXP_COUNT(t.csv, ',') + 1
) seq
WHERE REGEXP_SUBSTR(t.csv, '[^,]+', 1, seq.n) IS NOT NULL
ORDER BY t.id, seq.n;

Here, items is assumed to have an identifier column named id and a comma-delimited column named csv. The number of commas plus one estimates the number of fields. The final filter removes occurrences where the extraction finds no nonempty token. The ordering makes the intended sequence explicit; relational query results are not guaranteed to appear in a particular order without ORDER BY.

CROSS APPLY is available in Oracle Database 12c and later. If you are using an older release, or need to support more complex input, consider a PL/SQL function that returns a collection or a query design suited to that version. Test row correlation carefully: a hierarchical query written directly over a multirow table can create unwanted parent-child relationships if it is not constructed to generate occurrences independently for each source row.

Empty tokens, NULLs, and whitespace

The simple [^,]+ pattern deliberately skips empty fields. Consequently, red,,blue returns red and blue; it does not return a placeholder for the empty value between the commas. Likewise, a trailing delimiter in red,blue, does not produce a final empty token. This can change token positions, so this method is unsuitable when empty fields carry meaning.

Oracle also treats a zero-length character string as NULL in SQL for VARCHAR2 values. A null source value will therefore not behave like a distinct, present-but-empty string. Spaces are different: a token containing spaces is not automatically trimmed or considered empty by the regular expression. Apply TRIM if the data rules say surrounding spaces should be removed, and decide explicitly whether a spaces-only token should count as blank.

When empty fields must be preserved, use a tokenizer designed to return them, or write a carefully tested routine that locates delimiter positions and retains the text between them. Do not assume a regex that works for nonempty tokens will preserve leading, repeated, and trailing separators. Verify all three cases against the exact Oracle version and data types in use.

Delimiter rules and CSV limitations

A simple delimiter split is not the same as parsing CSV. In standard CSV-style data, a field may be enclosed in quotes and may itself contain a comma; quotes can also be escaped. The expression [^,]+ has no understanding of those rules, so it will split a quoted field at its internal comma. Use a proper CSV parser when the input follows CSV quoting conventions, rather than treating every comma as a separator.

For a multi-character separator, counting commas is not enough, and a single-character exclusion class cannot describe the separator as a unit. A regex-based approach may be possible, but its pattern and occurrence logic need to match the exact separator rules. If values can contain arbitrary text, a dedicated parser is generally easier to reason about than a growing regular expression.

Also decide whether separators can appear at the beginning or end, whether consecutive separators represent missing values, and whether spaces around a separator belong to the token. These are data-format decisions, not details Oracle can infer. Make them explicit in the query or preprocessing logic.

Choosing a SQL, PL/SQL, or application approach

For a one-off report or a small, predictable list, REGEXP_SUBSTR is compact and keeps the work in SQL. A PL/SQL function returning a SQL collection can be useful when splitting is repeated across many queries or when the parsing rules require state, such as preserving quoted fields. Oracle Application Express installations also provide the APEX_STRING package, including split-related functionality; that is an APEX package, not a universal built-in function available in every Oracle database.

If the input is already a JSON array, Oracle’s JSON features, such as JSON_TABLE, are usually a more appropriate way to turn its elements into rows. JSON is structured data with defined escaping rules, whereas comma-separated text is only a convention unless an agreed format defines its syntax. Do not convert arbitrary delimited text to JSON by simply placing brackets around it; embedded quotes, backslashes, and separators need correct encoding.

For a lasting data model, consider storing each item as a separate child-table row rather than packing a variable-length list into one column. A normalized design supports constraints, joins, filtering, and indexing more naturally. Splitting at query time can be a practical bridge for imported or legacy data, but repeatedly parsing the same field can add work and make validation harder.

Performance and maintainability

Regular expressions are convenient, but they do more work than a straightforward search for a literal delimiter in some workloads. A small report may not make that difference noticeable; a query that splits many rows repeatedly can. Measure performance with representative data, and compare the query with a purpose-built parser if parsing becomes a significant part of execution time. Avoid assuming that a particular expression will be faster without checking the actual workload.

Generated occurrences also need a sensible upper bound when one is known. The comma-count method creates one candidate row per comma plus one, then the extraction filters missing tokens. For sparse, very large, or malformed values, that can mean unnecessary row generation. Keep the parsing expression in one place where possible, document the delimiter rules, and test null input, long input, repeated delimiters, and any allowed special characters.

Common mistakes to avoid

  • Expecting a built-in function named SPLIT: Oracle SQL does not offer a general-purpose function with that name for arbitrary delimited strings.
  • Assuming empty fields survive: A pattern using + matches one or more characters, so empty fields disappear.
  • Using a simple comma split for CSV: Quoted commas and escaped quotes require CSV-aware parsing.
  • Forgetting row association: When splitting a column, generate token positions separately for each source row.
  • Expecting stable order without a sort: Include an occurrence number and use it in ORDER BY when output order matters.
  • Confusing spaces with empty strings: Trim or validate whitespace explicitly according to the data rules.

Frequently Asked Questions

Does Oracle SQL have a SPLIT function?
No general-purpose SQL SPLIT function is built into Oracle for turning arbitrary delimited text into rows. REGEXP_SUBSTR is a common SQL method, while packages such as APEX_STRING are available only in environments where the relevant package is installed.

How do I split a comma-separated string into rows?
Use REGEXP_SUBSTR(value, '[^,]+', 1, occurrence) to extract each nonempty token and generate occurrence numbers with a hierarchical query. For table columns, generate those numbers per source row, for example with CROSS APPLY on supported Oracle versions.

How can I keep empty values between delimiters?
The common [^,]+ pattern cannot preserve them because it matches only nonempty text. Use a tokenizer that explicitly retains empty fields or implement and test delimiter-position logic, including leading and trailing delimiters.

Can REGEXP_SUBSTR parse CSV correctly?
Not with the simple split pattern. CSV can contain quoted separators and escaped quotes, which require parsing the quoting rules. Use a CSV-aware parser for genuine CSV rather than splitting on every comma.

Can I split a string into separate columns?
Yes, but the query must request a fixed number of positions, typically by calling REGEXP_SUBSTR for occurrence one, two, and so on. If the number of values varies, returning one row per token is generally more flexible.

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.