Oracle Text to Columns: Split Strings into Fields

Oracle does not have a single database command called Text to Columns. To split a delimited string into separate query columns, use functions such as REGEXP_SUBSTR; for a file import, use an import tool such as Oracle SQL Developer or SQL*Loader. The right method depends on whether you need a one-time import, a reusable query, or a permanent change to stored data.

What “text to columns” means in Oracle

In spreadsheet software, Text to Columns usually means taking one cell and dividing it at commas, tabs, or another delimiter. In Oracle, the equivalent is a data-processing task rather than a dedicated worksheet-style command. A value such as Rina,30,West is stored as one character value in one column; a query can extract its parts into separate result columns, or an import process can load those parts into separate table columns.

These approaches are not interchangeable. A SELECT statement can present split values without changing the table. An UPDATE can write extracted values into existing columns, while a data-load process can parse incoming files as it inserts rows. It is generally safer to retain the original text in a staging table until the parsed values have been checked.

Split a delimited value into columns with REGEXP_SUBSTR

For a predictable delimiter-separated value, REGEXP_SUBSTR is a compact way to retrieve each position. This example assumes a table named source_data with an identifier column called id and a text column called line. It returns the first three comma-delimited fields as separate columns:

SELECT id,
       REGEXP_SUBSTR(line, '([^,]*)(,|$)', 1, 1, NULL, 1) AS name,
       REGEXP_SUBSTR(line, '([^,]*)(,|$)', 1, 2, NULL, 1) AS age_text,
       REGEXP_SUBSTR(line, '([^,]*)(,|$)', 1, 3, NULL, 1) AS region
FROM source_data;

The regular expression has two groups: ([^,]*) captures zero or more characters other than a comma, and (,|$) matches the comma separator or the end of the value. The final argument, 1, asks Oracle to return the first captured group. The occurrence argument—the fourth argument—selects the first, second, or third match.

For Rina,30,West, the result is Rina, 30, and West. For Rina,,West, the second field is empty. Oracle treats a zero-length character string as NULL, so that empty field appears as NULL in the result. If spaces around values are unwanted, wrap the expression in TRIM, for example TRIM(REGEXP_SUBSTR(...)).

Choose a method based on the delimiter and data

The regular-expression method is useful when the delimiter is consistent and fields may be empty. A simpler expression such as REGEXP_SUBSTR(line, '[^,]+', 1, 2) finds the second nonempty run of characters, but it skips empty fields. With Rina,,West, it can return West for the second occurrence, not the empty middle field. That difference matters if field position has meaning.

For a small, fixed number of fields and a simple delimiter, combinations of INSTR and SUBSTR are another option. They locate delimiter positions and return the text between them. This can be efficient and avoids regular-expression syntax, but expressions become lengthy when there are many columns, and extra care is needed when a delimiter is missing or appears more or fewer times than expected.

For a file that is being loaded, parsing during import is often preferable to splitting a large text column later. In Oracle SQL Developer, the table import wizard can map delimited-file fields to table columns; the exact screens and options vary by version. For repeatable or larger loads, SQL*Loader and external tables are common Oracle tools. They let the load definition describe fields and delimiters, and can make error handling and reruns more manageable than hand-editing individual rows.

Handle spaces, data types, and missing fields

Splitting produces character values unless you explicitly convert them. A field that looks like a number is still text when returned by REGEXP_SUBSTR. Convert it only after considering invalid values and formatting. For example, TO_NUMBER can convert a clean numeric field, but a stray word or an unexpected decimal separator can cause a conversion error. Validate source values and use appropriate number-format and NLS settings when the input format is not the session default.

Dates need the same attention. Do not rely on implicit conversion, because Oracle may interpret a date string according to the session’s date format. Use TO_DATE with a format model that matches the input, such as DD-MON-YYYY for a value like 02-NOV-2026. Confirm the actual incoming pattern first; a different ordering of day, month, and year requires a different model.

Leading or trailing spaces: apply TRIM if the data definition says spaces are insignificant.
Empty fields: use a pattern that accounts for zero-length fields, and remember that Oracle represents an empty string as NULL.
Missing trailing fields: decide whether they should become NULL, be rejected, or be treated as malformed input.
Unexpected field counts: count delimiters or validate rows before loading them into a fixed-column table.

Delimited text is not always a simple split

A comma-separated file may follow CSV rules: a comma inside quotation marks can be part of a field, and quotation marks may be escaped. For example, 17,"Portland, Oregon",Active contains three CSV fields, not four. The simple regular expression above does not understand quoted CSV syntax; it will treat the comma inside the quoted address as a separator. Similar problems arise with embedded line breaks, escaped quotes, or a delimiter that can appear in free-form text.

For properly quoted CSV, use a CSV-aware import mechanism rather than assuming every comma marks a boundary. Configure the delimiter, quote character, character set, and column formats for the source file. If the data is already in a table as raw text, consider parsing it with a method that understands the exact file specification or correcting the upstream process. A regular expression that works for uncomplicated values is not automatically a correct CSV parser.

Return split values as rows instead of columns

Sometimes the desired result is one row per token, not one output column per token. For example, a list of tags might be easier to filter or join when each tag occupies its own row. That is a different transformation from Text to Columns. Oracle can generate occurrences of a token with regular-expression functions and a row generator, but the query must handle empty tokens, row identity, and the maximum number of values correctly.

For frequently queried lists, storing multiple values in one delimited column is often the underlying problem. A normalized design stores each item in a related child table, with one row per item and a key linking it to the parent record. This makes searching, indexing, joining, and validating individual values more straightforward. Splitting at query time can still be useful for legacy data or a one-off report, but it is not a substitute for a suitable data model.

Make changes safely and check the results

A SELECT query is a good first step because it displays the proposed fields without altering stored data. Compare the extracted values with the source text, especially for rows with missing delimiters, extra delimiters, spaces, or unexpected characters. Also check the number of rows before and after any load; a parsing operation should not silently lose records.

If the results need to be saved, load them into a staging table or populate new columns only after validation. Before an UPDATE, preserve a copy of the source data and use a WHERE clause that identifies the intended rows. Test the conversion on a limited sample, check for NULLs and conversion errors, and commit only after reviewing the outcome. A split that is correct for most rows can still corrupt data when one row uses a different delimiter or field order.

For repeatable processes, record the input format and parsing assumptions alongside the load procedure: delimiter, quote rules, expected field count, character encoding, and date and number formats. This makes later troubleshooting easier and helps prevent a changed source file from being accepted under outdated assumptions.

Frequently Asked Questions

Does Oracle have a Text to Columns button?
No single Oracle Database command has that name. Use SQL string functions to split values in a query, or use a database import tool to parse a delimited file during loading.

Can REGEXP_SUBSTR split a comma-separated column into several columns?
Yes. Call it once for each field position and give each expression a different occurrence number. Choose a pattern that preserves empty positions if consecutive delimiters are possible.

How do I keep an empty field between two commas?
A pattern that allows zero characters before the delimiter, such as ([^,]*)(,|$), can match that position. Its returned empty string is NULL in Oracle, so the query cannot distinguish that empty field from every other NULL without additional handling.

Will this method correctly parse every CSV file?
No. A basic comma split does not recognize quoted commas, escaped quote characters, or embedded line breaks. Use a CSV-aware import process when the source follows those rules.

Does splitting with SELECT change the table?
No. A SELECT statement only returns a result set. To store separate values, use an import or an explicit INSERT or UPDATE after checking the parsed data.

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.