Oracle SPLIT Function: Split Strings in SQL

Formulas & functions · Updated November 22, 2026

Oracle Database does not provide a general built-in SQL function named SPLIT for turning a delimited string into separate values. If Oracle APEX is installed, use APEX_STRING.SPLIT; otherwise, common SQL approaches include REGEXP_SUBSTR for simple, nonempty tokens or a recursive query when empty fields and their positions matter.

What “SPLIT” Means in Oracle

In many programming languages and some database systems, a split function accepts a string and a delimiter, then returns the pieces. Oracle SQL does not have one universal function with that name and behavior. The right solution depends on which Oracle tools are available and what the output needs to look like: a collection, rows in a query, or values used inside PL/SQL.

This distinction matters when copying examples from other platforms. SQL Server has a built-in STRING_SPLIT function, while Oracle SQL users often see examples using REGEXP_SUBSTR or a package supplied by Oracle APEX. Those approaches are not interchangeable in every case. They can handle empty fields, delimiter patterns, and ordering differently.

For example, splitting red,green,blue on commas seems simple. But a value such as red,,blue, raises extra questions: should the empty field between the commas be returned? Should the final empty field be retained? If the pieces are later joined to other table data, does their original position matter? Choose a method with those requirements in mind.

Using APEX_STRING.SPLIT

If Oracle APEX is installed and the calling schema can access its utility package, APEX_STRING.SPLIT is a direct option. It returns an APEX_T_VARCHAR2 collection rather than a single string. In SQL, you can expose the collection as rows with the TABLE operator:

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

The query returns one row for each token. In this example, column_value contains red, green, and blue. Use an explicit separator so the intended delimiter is clear. In PL/SQL, the returned collection can also be assigned to a variable of the appropriate collection type and processed in a loop.

There is an important separator detail: in APEX_STRING.SPLIT, a separator longer than one character is treated as a regular expression. A comma is a straightforward one-character separator. A multi-character separator containing regular-expression metacharacters may need escaping, or it may match something other than the literal text you intended. Check the Oracle APEX documentation for the installed version when relying on package details.

This function belongs to Oracle APEX, not to the general Oracle Database SQL function set. On a database without APEX, or where the database schema cannot access the APEX package, the query will fail because the package is unavailable or not visible. In that case, use a SQL-only method or ask the database administrator about the environment and permissions. Do not assume that APEX is installed just because a database is Oracle.

Splitting Simple Strings with REGEXP_SUBSTR

For a known one-character delimiter and tokens that are never empty, REGEXP_SUBSTR can extract each token. The following example uses a comma and assigns each match a position with LEVEL:

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

The regular expression [^,]+ means “one or more characters that are not commas.” The fourth argument to REGEXP_SUBSTR selects the match number, so level 1 returns the first token, level 2 the second, and so on. The CONNECT BY clause generates successive levels until there is no next match. The result is ordered by its position, rather than relying on a database’s incidental row-return order.

This is a compact technique, but its behavior is a limitation: because the pattern requires one or more characters, it skips empty fields. Splitting red,,blue this way returns red and blue, not a blank token between them. It also does not return a final blank value for red,. Use it only when empty fields are invalid or intentionally irrelevant.

For a table of input strings, replace the literal with a column and correlate the generated rows to each source row. Take care with hierarchical queries over multiple source rows: an uncorrelated CONNECT BY can create unintended combinations. Test the query on several records, or use a recursive subquery factoring approach that carries each source row’s identifier through the split.

Preserving Empty Fields and Token Positions

If empty fields are meaningful, a delimiter-by-delimiter query can preserve their positions. The recursive example below assumes a table called source_data with a numeric or character id and a text_value column containing strings no longer than 4,000 characters. Appending a comma gives even the last field a delimiter to find:

WITH split (id, rest, token, token_no) AS (
  SELECT id,
         text_value || ',',
         CAST(NULL AS VARCHAR2(4000)),
         0
  FROM source_data
  UNION ALL
  SELECT id,
         SUBSTR(rest, INSTR(rest, ',') + 1),
         SUBSTR(rest, 1, INSTR(rest, ',') - 1),
         token_no + 1
  FROM split
  WHERE rest IS NOT NULL
)
SELECT id, token_no, token
FROM split
WHERE token_no > 0
ORDER BY id, token_no;

At each recursive step, the query takes the text before the first comma as the next token and removes that part from rest. For red,,blue,, it produces three positions: red, an empty value, and blue, followed by the final empty field. Oracle treats a zero-length character string as NULL, so the empty token appears as null in the result. Its position still indicates that a field was present.

This example is for comma-delimited input and uses a VARCHAR2(4000) placeholder. Adapt the datatype and maximum length to the actual column. CLOB input, very long values, or delimiters made up of multiple characters need a suitable variation. Also decide what a null input means in your application: this query treats it like a string that yields one empty field after the delimiter is appended. If null should produce no rows, add that rule explicitly to the anchor query.

Choosing the Right Method

Start with the output and data rules, not just the shortest expression. A method that silently drops blank fields can corrupt a positional list, while a regular expression may interpret a multi-character separator as a pattern rather than literal text.

  • Use APEX_STRING.SPLIT when APEX is available, its package is accessible, and returning a collection as rows or PL/SQL values suits the task.
  • Use REGEXP_SUBSTR for a compact query when the delimiter is simple and empty tokens can safely be ignored.
  • Use a recursive query when a plain SQL result must retain field positions and account for consecutive or trailing delimiters.
  • Use application code or a purpose-built parser when the input follows a complex format, such as quoted CSV fields containing commas.

The last point is easy to overlook. A comma-separated list is not necessarily a CSV file. In real CSV, a value may be quoted and contain a comma, and quotes may be escaped. A basic split on every comma will break such a value into the wrong pieces. For actual CSV input, use a CSV-aware import or parsing facility rather than treating punctuation alone as the format specification.

Common Problems and Data Considerations

Whitespace is not automatically removed by these methods. If an input is red, green,blue, the second token may begin with a space. If surrounding spaces are not part of the intended value, trim the tokens in the query or normalize the input. Do not trim blindly if spaces are meaningful, for example in free-form text.

Regular expressions also need care when the delimiter is not a comma. In REGEXP_SUBSTR, the delimiter appears inside a regular expression, so characters such as a period, a plus sign, or a vertical bar can have special meanings. A pattern written for one delimiter may therefore split incorrectly for another. For a fixed, simple delimiter, test the pattern with representative values, including delimiter characters that have special regex meaning.

Finally, splitting is a parsing operation, not usually a substitute for a normalized data model. If an application repeatedly searches, joins, validates, or updates individual items, storing them in a child table—one row per item, linked by an identifier—is often easier to maintain than storing a delimited list in one column. A split query can be useful for importing or reporting, but repeatedly parsing the same stored text adds complexity and makes constraints harder to enforce.

Ordering, Performance, and Version Notes

SQL results have no guaranteed order unless the query includes an ORDER BY. If token order matters, return a token number and sort by it. The regular-expression example uses LEVEL; the recursive example carries token_no. With APEX_STRING.SPLIT, the collection provides the values, but a plain TABLE query should not be treated as an ordered result unless the query explicitly establishes an ordering strategy.

For short strings and occasional queries, any of these approaches may be adequate. Repeated regular-expression evaluation across many rows can be more work than a simple delimiter scan, while recursive queries add their own processing overhead. The actual impact depends on row counts, string lengths, database version, and the rest of the query. If splitting is part of a large or frequent workload, compare execution plans and timings with representative data rather than assuming one technique is always fastest.

Oracle environments differ: the database release, installed APEX release, schema grants, and SQL context can affect which option is available. Confirm package access and test against the same environment where the query will run. In particular, examples that work in an APEX parsing schema may not work unchanged in another schema or in a database that has no APEX installation.

Frequently Asked Questions

Does Oracle SQL have a SPLIT function?
No general built-in SQL function named SPLIT is available for splitting arbitrary strings. Oracle APEX supplies APEX_STRING.SPLIT; otherwise, SQL techniques such as REGEXP_SUBSTR or a recursive query are commonly used.

How do I split a comma-separated string into rows?
Use TABLE(APEX_STRING.SPLIT(value, ',')) if the APEX package is accessible. Without it, REGEXP_SUBSTR with a hierarchical query works for nonempty comma-separated tokens; choose a method that accounts for empty fields if those must be retained.

Does REGEXP_SUBSTR keep empty values?
Not with the common pattern [^,]+, because the plus sign requires at least one character. Consecutive or trailing delimiters therefore do not produce blank tokens. Use a delimiter-scanning approach or another method designed to preserve positions.

Can APEX_STRING.SPLIT split on a multi-character delimiter?
It can accept a separator longer than one character, but that separator is treated as a regular expression. Escape regex metacharacters when you need a literal match, and verify the behavior for the installed APEX version.

How can I keep the original order of split values?
Return a position for each token and include it in an ORDER BY. The examples using LEVEL or token_no show this pattern. Without an explicit sort, SQL does not promise that rows will be returned in token order.

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.