Oracle Split String Example: SQL Methods That Work

How-to basics · Updated November 22, 2026

To split a comma-separated string into rows in Oracle, use REGEXP_SUBSTR with a hierarchical query: SELECT LEVEL AS position, REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, LEVEL) AS token FROM dual CONNECT BY LEVEL <= REGEXP_COUNT('red,green,blue', ',') + 1; It returns one row per nonempty token: red, green, and blue.

A Basic Oracle SQL Split String Example

Oracle SQL does not provide a general-purpose built-in function named STRING_SPLIT like SQL Server does. A common Oracle approach is to ask REGEXP_SUBSTR for the first match, then the second, then the third, using LEVEL to number the matches. The query above uses DUAL, Oracle’s one-row table, as the input for that sequence.

The regular expression [^,]+ means one or more characters that are not commas. In the function call, the first argument is the source string, the second is the pattern, the third is the starting character position, and the fourth is which match to return. The query starts at character one and uses LEVEL as the match number. REGEXP_COUNT counts commas so the query knows how many rows to generate: the number of separators plus one.

For the example value, the result is:

  • Position 1: red
  • Position 2: green
  • Position 3: blue

To return a particular item rather than a row per item, request a fixed occurrence. For example, REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, 2) returns green. It returns null if that occurrence does not exist.

How the Regex and Row Generation Work

REGEXP_SUBSTR returns one substring at a time; it does not by itself turn a string into multiple rows. The hierarchical query supplies the repeated calls. In a query without a parent-child relationship, CONNECT BY LEVEL generates levels starting at one and continues while its condition is true. Here, the condition limits the number of levels to the number of expected tokens.

The plus sign in [^,]+ matters. It requires at least one non-comma character, so consecutive delimiters do not produce empty matches. If the input is red,green,blue, there are two commas and three tokens. If there is no comma, there is one expected token. This is why adding one to the separator count works for ordinary, nonempty, comma-separated values.

This pattern is useful when fields are simple text and commas are always separators. It is not a CSV parser. In a real CSV file, a comma inside quoted text may be part of a field, and quotes may be escaped. A regular expression like this does not apply CSV quoting rules; use a proper file-loading or parsing process when values can contain quoted delimiters.

Splitting a Column for Every Table Row

Suppose a table named source_table has an id column and a csv_text column, with one comma-separated string in each row. On Oracle 12c and later, a lateral row source with CROSS APPLY can split each value and retain its parent ID:

SELECT t.id, x.position, x.token
FROM source_table t
CROSS APPLY (
  SELECT LEVEL AS position,
         REGEXP_SUBSTR(t.csv_text, '[^,]+', 1, LEVEL) AS token
  FROM dual
  CONNECT BY LEVEL <= REGEXP_COUNT(t.csv_text, ',') + 1
) x;

The result contains the original ID, the token’s position, and its text. Keeping the position is helpful when the order matters or when you need to compare corresponding items across rows. CROSS APPLY evaluates the row source using the current table row, so each source value gets its own generated sequence.

This version still has the same nonempty-token assumption as the basic example. It also produces no useful token for a null source value: Oracle treats a zero-length character string as null, and arithmetic involving a null result from REGEXP_COUNT does not generate the expected levels. If null or empty source values should appear in the output, decide explicitly whether they should be excluded, represented as a null token, or handled separately.

Empty Fields, Consecutive Delimiters, and Trailing Commas

A frequent surprise is that [^,]+ skips empty fields. For input red,,blue, the two nonempty matches are red and blue; the expression does not return an empty second value. A trailing delimiter such as red, creates the same issue: the separator count suggests two positions, but there is no second nonempty match. The generated row for that position has a null token.

That behavior may be correct if empty fields are invalid or should be ignored. If field positions must be preserved, do not assume the basic regex example is suitable. Choose a method that explicitly calculates the text between each pair of delimiter positions, and define how an empty field should be represented. In Oracle, an empty string in a character column is treated as null, so preserving the position does not mean Oracle can store a distinct zero-length VARCHAR2 value.

Before writing the split query, check the input contract. Is a,,b two values or three, with a blank middle value? Does a, mean a final blank field or malformed input? Are spaces around fields significant? These are data rules, not details that a splitting function can infer. For data where empty positions matter, test consecutive, leading, and trailing delimiters against the chosen solution and verify both the returned values and their positions.

Different or Multi-Character Delimiters

For a one-character delimiter such as a pipe, update both the separator count and the regular expression. In a regex character class, the pipe is literal, so a pattern can be written as [^|]+, with REGEXP_COUNT(value, '|') used to count separators. Some characters have special meaning in regular expressions, however, and may need escaping or a different pattern. Test the pattern with actual input rather than treating every punctuation mark as interchangeable.

A multi-character delimiter, such as || or ::, is not a simple character-class case. The pattern must identify the full separator, and the number of pieces must be based on occurrences of that full separator rather than on individual characters. For literal delimiters, an approach based on INSTR and SUBSTR can be easier to reason about because those functions search for a literal substring. Confirm that the method handles leading, repeated, and trailing delimiters the way the data specification requires.

Choosing Between SQL, APEX, and Other Options

Use the regular-expression query when you need a compact SQL solution for simple, nonempty tokens. It is often convenient for ad hoc queries and reporting, and it can run without installing an application-specific package. For a recurring transformation, consider whether the delimited format should instead be normalized into a child table. Storing one value per row avoids reparsing the same string for filtering, joins, and validation.

If Oracle Application Express is installed and available to the schema, its APEX_STRING.SPLIT function is another option. It returns an APEX collection that can be queried as a row source, for example with TABLE(APEX_STRING.SPLIT('red,green,blue', ',')); the returned collection’s value column is commonly referenced as COLUMN_VALUE. This is convenient in APEX environments, but it is not a universal Oracle Database function. Check that the APEX package is installed, accessible, and appropriate for the database where the SQL will run.

DBMS_UTILITY.COMMA_TO_TABLE is not a general replacement for splitting arbitrary text. It is intended for comma-separated Oracle names and has restrictions appropriate to that use. Likewise, JSON functions are a better fit when the source is actually a JSON array, not when a string merely happens to contain commas. Choose a parser that matches the input format rather than repurposing a specialized function.

Performance and Practical Limitations

For short strings and modest row counts, the regular-expression method is readable and usually a reasonable starting point. On large tables, splitting every row can require substantial repeated work, particularly if the query applies regular-expression functions to many long values. Filter the source rows before expanding them when possible, and avoid running the split repeatedly in separate parts of a report or query.

Consider the broader data design if the same column is regularly searched for individual values. A condition such as a wildcard search through comma-separated text is difficult to validate and usually cannot use an ordinary index as efficiently as a properly modeled relationship. A table with one row per value allows normal joins, constraints, and indexes. Splitting at query time is useful for importing or presenting legacy data, but it does not automatically make a delimited column a good storage model.

Oracle version and environment also matter. The basic regular-expression functions have been available across many Oracle releases, but the lateral CROSS APPLY example requires Oracle 12c or later. APEX functions require APEX. If a query must run on an older database or in a restricted schema, confirm the supported syntax and available packages on that system before adopting a solution.

Common Mistakes to Avoid

  • Expecting empty fields: [^,]+ deliberately matches nonempty text. It skips consecutive separators and cannot preserve every blank field by itself.
  • Counting the wrong separator: The separator used in REGEXP_COUNT must match the one being split. Otherwise, the query can generate too few or too many levels.
  • Assuming commas always separate fields: Quoted CSV values may contain commas, so this simple expression will split them incorrectly.
  • Ignoring spaces: A token like green includes its leading space. Use trimming only if whitespace is not meaningful in the data.
  • Copying SQL Server syntax: Oracle does not have SQL Server’s STRING_SPLIT function under that name.

Frequently Asked Questions

Does Oracle have a built-in SPLIT function?
Oracle SQL does not offer a general built-in STRING_SPLIT function for arbitrary delimited text. REGEXP_SUBSTR with row generation is a common SQL technique; APEX installations also offer APEX_STRING.SPLIT.

How do I get the second value from a comma-separated string?
For a simple string with nonempty values, use REGEXP_SUBSTR('red,green,blue', '[^,]+', 1, 2). It returns green. The occurrence argument is two because it asks for the second regex match.

How do I split a string into rows rather than columns?
Generate one row for each token with CONNECT BY LEVEL and select the corresponding REGEXP_SUBSTR occurrence. Use the table-row example above when each input row has a separate string to split.

Will the regular-expression example preserve blank values?
No. The pattern [^,]+ requires one or more non-comma characters, so it skips blank fields. Use a position-based method designed for empty fields if their positions are meaningful, and account for Oracle’s treatment of empty strings as null.

Can I use this for CSV files with quoted commas?
Not safely. The example treats every comma as a delimiter and does not interpret CSV quotes or escaped quote characters. Use a CSV-aware import or parser when fields can contain quoted commas.

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.