Oracle SQL Split Function: How to Split Text into Rows

Formulas & functions · Updated November 22, 2026

Oracle SQL has no single SPLIT() function that works portably across releases and installations. For ordinary delimited text, use REGEXP_SUBSTR to extract tokens; if Oracle Application Express is installed, APEX_STRING.SPLIT is another option.

What Oracle means by a split function

In many programming languages, a split function takes a string and a separator, then returns the pieces as a list or array. SQL queries can use those pieces in different ways: return one requested piece in a column, or turn each piece into a separate row. Those are related tasks, but the SQL needed for them is not identical.

Oracle Database has built-in string functions such as SUBSTR, INSTR, and REGEXP_SUBSTR, but availability and behavior can vary when you compare core SQL, optional products, and different database releases. In particular, an example that calls APEX_STRING.SPLIT depends on the APEX package being available. There is no one split expression readers can assume will work unchanged in every Oracle environment.

For a broadly useful SQL solution, REGEXP_SUBSTR finds a requested occurrence of a regular-expression pattern. A pattern such as [^,]+ means one or more characters that are not commas. It is a good fit when the input is a simple comma-separated list and every item is nonempty.

Extract one token from a string

To get a particular item, give REGEXP_SUBSTR the source string, a pattern, the starting position, and the occurrence number. With a comma-separated value in a literal string, this query returns the second item, green:

SELECT REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, 2) AS token
FROM dual;

The first argument is the text to search. The pattern matches a run of non-comma characters, the third argument starts the search at character 1, and the fourth asks for the second match. Change the final number to 1 for red or 3 for blue. If the requested occurrence is not found, the function returns NULL.

This pattern treats every comma as a separator, not as a character that might appear inside a quoted field. For example, it cannot correctly parse a CSV value such as North, Inc. when that value is quoted or otherwise contains a comma that belongs to the data. A simple delimiter expression is appropriate only when the input format guarantees that the delimiter does not occur within an item.

Return each token as a separate row

To turn a list into rows, Oracle can generate occurrence numbers with a hierarchical query and use each number with REGEXP_SUBSTR. For one known input string, this example returns three rows:

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

LEVEL starts at 1 and increases for each generated row. The CONNECT BY condition stops when there is no matching token at the current occurrence. This is concise for a single value, but a production query often starts with one delimited string per source-table row.

On Oracle Database 12c and later, a lateral CROSS APPLY can generate occurrence numbers for each source row. Assume a table named items has a unique id and a csv column containing comma-separated, nonempty values:

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

REGEXP_COUNT counts the commas, so adding 1 gives the number of expected tokens when values are nonempty. The apply operation calculates that sequence separately for each row of items. This example intentionally assumes there are no empty items: the pattern skips empty fields, so a value such as a,,b does not produce a faithful three-position result. If empty fields matter, use a method designed to preserve them rather than relying on this pattern.

Choose between regular expressions and string positions

REGEXP_SUBSTR is often the clearest choice when the separator or token pattern is not trivial. It can match more than one kind of separator, and it makes the requested occurrence easy to see. Its flexibility also means the pattern must be understood as a regular expression: punctuation such as a period or a plus sign can have special meaning and may need escaping.

For a fixed, simple separator and a small, known number of fields, INSTR and SUBSTR can be a reasonable alternative. INSTR locates a delimiter, while SUBSTR extracts the characters before or after that location. This can make the boundary logic explicit, but expressions become cumbersome when the desired item number varies or the number of items is unknown.

  • Use REGEXP_SUBSTR when you need a broadly applicable SQL expression and the values follow a simple token pattern.
  • Use INSTR with SUBSTR when there are only a few fixed positions and literal delimiter handling is preferable.
  • Use an installed package or a structured input format when the data has quoting rules, escaped separators, or empty values that must be preserved.

Empty items, trailing delimiters, and NULL

Empty items are a common source of incorrect results. In the expression [^,]+, the plus sign requires at least one character. Thus, for alpha,,gamma, the matches are alpha and gamma; the empty second field is not a match. A trailing delimiter, as in alpha,, also represents a potentially meaningful empty final field in some data formats, but the pattern does not return it as an ordinary token.

Decide what an empty field means before selecting a technique. It might be invalid input, a missing value that should be discarded, or a real position that must remain in the result. If positions matter, simply counting delimiters and extracting nonempty matches is not sufficient. Test leading, repeated, and trailing delimiters as well as an entirely empty string and a SQL NULL.

There is another important distinction: SQL NULL is not necessarily the same thing as an empty string in every context. Oracle treats a zero-length character string as NULL for SQL character data, so an empty input can behave differently from a string containing delimiters. Do not assume a generic split example will preserve blank values exactly as a list-processing function in another language might.

APEX_STRING.SPLIT and JSON_TABLE

If Oracle Application Express is installed and its package is available to the schema, APEX_STRING.SPLIT can return the pieces as a collection. For example, this query projects the collection elements into rows:

SELECT column_value AS token
FROM TABLE(apex_string.split('red,green,blue', ','));

This is an APEX utility, not a universal core SQL function. Check that APEX is installed, that the package is accessible, and that its behavior suits the delimiter and empty-field requirements. The package documentation for the installed APEX version is the authority for its parameters and separator rules. Avoid copying an APEX example into a database where that package is unavailable.

JSON_TABLE is useful when the source is already a JSON array, rather than an arbitrary delimited string. For example, a JSON value such as ["red","green"] has explicit array structure that SQL can project into rows. Turning comma-separated text into JSON first is not automatically safe: quotes, backslashes, and other special characters must be escaped correctly. If the source format is under your control, storing items as a proper child table or valid JSON array is usually clearer than repeatedly parsing a delimited column.

Performance and data-design considerations

Splitting text inside a query is convenient, but parsing every row at query time has a cost. Regular-expression matching generally does more work than locating a literal delimiter, and expressions repeated in a select list, filter, or join may be evaluated more than once. For a small report or a one-off migration, that trade-off may be entirely reasonable. For a frequently queried, large table, measure the query with representative data rather than assuming one technique will be faster.

Also consider what the delimited column represents. If each item is an independently searchable value, a normalized design with one row per item makes filtering, joining, validation, and indexing more straightforward. A split expression can convert legacy or external input into rows, but it does not provide the same data integrity as storing those values separately. If the application must retain a delimited format, validate it when data enters the database and document whether spaces, duplicates, empty items, and case differences are allowed.

Common mistakes to avoid

  • Assuming SPLIT() is universal: Check the database release and installed components. A package name and a built-in function are not interchangeable.
  • Using a regex pattern without checking its meaning: In [^,]+, the comma is literal, but other punctuation can be special in a regular expression.
  • Ignoring empty fields: A pattern that matches one or more characters skips blank items and can shift the apparent positions.
  • Treating CSV as simple comma-separated text: Quoted values can contain commas, escaped quotes, and line breaks. A basic regex split is not a complete CSV parser.
  • Assuming delimiter counts always equal returned tokens: That relationship holds for nonempty fields under the stated assumptions, not for every input.

Frequently Asked Questions

Does Oracle SQL have a built-in SPLIT function?
There is no single portable SPLIT() expression to assume across Oracle environments. For core SQL, REGEXP_SUBSTR is a common approach. Check the SQL reference for your exact database release and distinguish built-in functionality from the APEX package.

How do I get the second value from a comma-separated string?
Use REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, 2) to get green, provided the values are nonempty and commas cannot occur inside a value.

How do I split a string into rows?
Generate occurrence numbers and pass each one to REGEXP_SUBSTR. A hierarchical query works for one value; CROSS APPLY can generate the occurrences separately for each input row on Oracle Database 12c and later.

Can REGEXP_SUBSTR preserve empty values?
The common pattern [^,]+ cannot: it matches one or more non-comma characters, so it skips empty fields. If blank positions matter, choose and test a method that explicitly preserves them, including leading and trailing blanks.

Can I use APEX_STRING.SPLIT without Oracle APEX?
No. It is an Oracle Application Express package, so it is usable only when APEX is installed and the relevant schema has access. Otherwise use core SQL methods or a suitable data structure.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.