Oracle SQL Text to Columns: Split Delimited Data

To split delimited text into separate columns in Oracle SQL, use INSTR to find delimiter positions and SUBSTR to extract each field; for simple, nonempty fields, REGEXP_SUBSTR is a shorter alternative. Oracle SQL does not have a single Excel-style “Text to Columns” command: you write a query that returns one expression for each output column.

What “text to columns” means in Oracle

Suppose a table contains a value such as red|green|blue in one column, and an application needs the three values in separate result columns. A SQL query can calculate those columns when it runs, without changing the stored value. That is often the right approach for reporting or a one-off extract.

SQL result columns have names and types defined by the query. If the split values must be stored permanently, the query result can instead be used in an INSERT or an UPDATE, after checking that the target table and data rules are appropriate. Splitting a string in a SELECT does not, by itself, modify the source table.

Before choosing a method, identify the delimiter, the expected number of fields, whether empty fields are meaningful, and whether a field can contain the delimiter as data. Those details determine whether a small substring expression is sufficient or a proper file parser is needed.

Split a fixed number of fields with INSTR and SUBSTR

For a predictable number of fields, INSTR and SUBSTR make the parsing logic explicit. INSTR locates a character or a particular occurrence of it; SUBSTR returns part of the string. In the example below, the input has exactly two pipe delimiters, producing three fields.

WITH source_data AS (
  SELECT 'red|green|blue' AS txt FROM dual
)
SELECT
  SUBSTR(txt, 1, INSTR(txt, '|') - 1) AS first_value,
  SUBSTR(
    txt,
    INSTR(txt, '|') + 1,
    INSTR(txt, '|', 1, 2) - INSTR(txt, '|') - 1
  ) AS second_value,
  SUBSTR(txt, INSTR(txt, '|', 1, 2) + 1) AS third_value
FROM source_data;

The result is red, green, and blue. The first INSTR call finds the first pipe; the call with the fourth argument set to 2 finds the second. The length for the middle substring is the distance between those positions minus the delimiter itself.

Replace source_data with your table and txt with its column name. This pattern is easy to review and avoids regular-expression syntax, but it assumes the delimiters are present in the expected positions. For input with a variable number of fields or missing delimiters, add explicit validation or use a different approach rather than assuming every row is well-formed.

Use REGEXP_SUBSTR for simple repeated fields

When each field is nonempty and the number of output columns is known, REGEXP_SUBSTR can extract fields by occurrence. This query returns the first three nonempty values separated by commas:

SELECT
  REGEXP_SUBSTR(csv_text, '[^,]+', 1, 1) AS first_value,
  REGEXP_SUBSTR(csv_text, '[^,]+', 1, 2) AS second_value,
  REGEXP_SUBSTR(csv_text, '[^,]+', 1, 3) AS third_value
FROM my_table;

The pattern [^,]+ means “one or more characters that are not a comma.” The final argument in each call selects the first, second, or third match. If a row has fewer matches than requested, the corresponding expression returns NULL. Oracle regular-expression functions have been available for many releases, but check the documentation for the specific Oracle Database version and character set in use if your application depends on detailed pattern behavior.

This compact pattern does not preserve empty fields. For example, splitting alpha,,gamma with [^,]+ returns alpha as the first match and gamma as the second; the empty second field is skipped. That shifts later values into the wrong output columns. Use position-based parsing or a pattern designed and tested for empty fields when empty values carry meaning.

Choose based on the input format

There is no universally best split expression. Match the method to the data contract, not just to the shortest query. Consider these factors:

Fixed count and simple delimiter: Use INSTR and SUBSTR when the number of fields is known and the format is stable.
Simple, nonempty tokens: Use REGEXP_SUBSTR when the regular-expression pattern makes the query clearer and empty fields are not possible.
Variable number of tokens: Return one row per token, or load the data into a staging structure, instead of inventing an unlimited number of output columns.
Quoted CSV fields: Use a CSV-aware import or parsing tool if commas can appear inside quoted values. A basic delimiter split cannot reliably interpret CSV quoting and escaped quotes.
Data quality requirements: Check field count, length, and type conversions before inserting parsed values into constrained columns.

A delimiter-separated string is not automatically a well-defined data format. For example, a comma inside an address may be a separator or part of the address, depending on the format rules. Clarifying those rules first prevents a query that appears to work on a few rows but misreads real data.

Return tokens as rows when the count varies

Sometimes the goal is not to create a fixed set of columns. If a row can contain any number of values, a row-per-token result is usually more natural for SQL. For a single input string with nonempty pipe-delimited values, this Oracle hierarchical query returns one row for each token:

WITH source_data AS (
  SELECT 'red|green|blue' AS txt FROM dual
)
SELECT REGEXP_SUBSTR(txt, '[^|]+', 1, LEVEL) AS token
FROM source_data
CONNECT BY LEVEL <= REGEXP_COUNT(txt, '[|]') + 1;

The result has three rows. REGEXP_COUNT counts the delimiters, and the number of tokens in this simple format is one more than that count. LEVEL supplies successive occurrence numbers to REGEXP_SUBSTR. The pattern deliberately extracts nonempty tokens, so this example is not suitable when leading, repeated, or trailing delimiters represent empty values.

For data from a table rather than one row from DUAL, splitting each source row independently requires correlating the generated token rows to a unique source-row key. Hierarchical queries that are not correlated carefully can produce unwanted combinations or repeated results. Test with multiple source rows, duplicate text values, and empty values before using a row-splitting query in production. If the data arrives in a file, a database loading tool may be simpler and safer than maintaining custom parsing SQL.

CSV quoting and empty fields are important exceptions

Expressions based on finding commas do not implement the complete CSV format. Consider Smith,"Jane, Ann",42: the comma between Jane and Ann is inside a quoted field, not a separator. A simple INSTR or REGEXP_SUBSTR split will treat it as a delimiter and produce incorrect columns. Quoted fields can also contain escaped quote characters, so handling them correctly requires more than stripping quote marks after a split.

For a genuine CSV file, use a CSV-aware import route, such as an appropriate Oracle loading utility or an application-side CSV parser, and configure its delimiter, quote, and escape rules. Oracle SQL Developer’s import options can also be useful for loading a file, but available screens and options can differ by version. Once parsed into proper columns, database queries no longer need to reinterpret the original CSV syntax.

Empty fields need an explicit policy too. In Oracle SQL, a zero-length character string is treated as NULL. A missing value, two consecutive delimiters, and an empty string therefore may not remain distinguishable after conversion to a regular character value. If those distinctions matter, retain additional indicators or use a staging format that records the original field positions and source text.

Convert extracted values to the right data types

Substring functions return character data. If a token represents a number or date, convert it only after considering malformed input and the required format. For instance, TO_NUMBER can fail on a nonnumeric token, and date conversion can depend on format models and session settings. Use explicit format models for dates rather than relying on the session’s default display or interpretation format.

For dependable loading, validate before converting. You might first select the raw tokens and identify rows that do not match the expected pattern, then convert only the verified values. On supported Oracle versions, conversion functions offer error-handling options, but their exact availability and syntax depend on the database release; consult that release’s SQL reference. Avoid silently mapping invalid data to a plausible value, because that makes later correction harder.

Also consider output size and column definitions. A token longer than its target column may cause an error during insertion, even if the split itself succeeds. Trimming whitespace is a separate decision: functions such as TRIM can remove spaces around a token, but should not be applied if those spaces are meaningful data.

Query-time splitting versus changing the table

A computed split in a SELECT is useful when the original combined string must remain unchanged or when only one report needs separate fields. It avoids a data migration and leaves the source row intact. Repeating the same parsing expression in many queries, however, can create inconsistent rules and make maintenance difficult.

If the values are logically separate attributes used for filtering, joining, validation, or updates, storing them in separate columns is generally easier to manage. A normalized design may instead store repeated values in child rows, with a key linking them to the parent record. The best design depends on the meaning and number of values; the important point is that SQL can calculate columns from text, but a calculated result is not the same thing as a well-structured stored model.

When moving from a combined field to separate columns, preview the parsed results, count delimiters, identify malformed rows, and check conversion failures before updating production data. Keep a recoverable copy or use a controlled migration process. Parsing logic should be based on documented input rules, not on assumptions inferred from a handful of sample records.

Frequently Asked Questions

Does Oracle have a built-in Text to Columns command?
Not as a general SQL statement that automatically creates columns from an arbitrary delimited string. Write a query using functions such as INSTR, SUBSTR, or REGEXP_SUBSTR, or use a suitable import tool for files.

How do I split a comma-separated value into three columns?
For simple, nonempty values, call REGEXP_SUBSTR(column_name, '[^,]+', 1, 1), then repeat with occurrence numbers 2 and 3. If empty fields or quoted commas are possible, that shortcut may return incorrect results; use parsing logic suited to the actual format.

Can Oracle split one string into multiple rows?
Yes. A hierarchical query using LEVEL, REGEXP_COUNT, and REGEXP_SUBSTR can return successive tokens as rows for a simple delimiter format. Correlate the generated rows to a unique key when processing a table with multiple source records.

Will splitting the text change the original table?
No. A SELECT that extracts substrings returns calculated columns only. To store the values separately, use an appropriate insert or update operation after validating the parsed data.

Can a basic split handle commas inside quoted CSV fields?
No. A basic split treats every comma as a delimiter and does not understand CSV quotes or escaped quotes. Use a CSV-aware loader or parser when those rules are part of the input format.

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.