Oracle String to Value: Converting Text in SQL

How-to basics · Updated November 22, 2026

In Oracle SQL, convert a string to a typed value with an explicit conversion function: use TO_NUMBER for numeric text, TO_DATE for date text, and TO_TIMESTAMP for timestamp text. For example, TO_NUMBER('245.75') returns a number, while TO_DATE('2026-11-03', 'YYYY-MM-DD') returns an Oracle DATE.

What converting a string to a value means

A string is character data, such as a value stored in a VARCHAR2 column. A number or date is a different data type with different operations and rules. Converting text to a value tells Oracle to interpret the characters as that type, so it can perform arithmetic, compare dates chronologically, or store the result in a typed column.

The right function depends on what the text represents. Numeric text should not be treated as a date, and a date-like string should not be converted to a number just because its characters include digits. Oracle’s conversion functions return typed values; they do not merely change how the same text is displayed.

  • Use TO_NUMBER(text) for integers and decimal numbers.
  • Use TO_DATE(text, format_model) for dates and times represented as text.
  • Use TO_TIMESTAMP(text, format_model) when fractional seconds matter.
  • Use TO_TIMESTAMP_TZ when the text includes a time-zone component.

If the source value is already stored as a number or date, do not convert it to text and back unless there is a specific reason. Keeping data in its natural type avoids unnecessary conversion and makes comparisons and calculations clearer.

Convert a string to a number

TO_NUMBER converts character data into Oracle’s NUMBER type. For straightforward numeric text, a simple call may be enough: TO_NUMBER('245.75'). In a query, the input is often a column, for example TO_NUMBER(amount_text). If the text has grouping separators, a currency symbol, or another defined presentation, supply a number format model that describes it.

Suppose amount_text contains the text 1,234.50, with a comma for grouping and a period for the decimal mark. A format model can identify those positions:

TO_NUMBER('1,234.50', '9G999D99', 'NLS_NUMERIC_CHARACTERS=''.,''')

Here, G represents the group separator and D represents the decimal separator. The third argument sets the numeric characters for this conversion, so the example does not have to rely on the session’s usual separator settings. The result is the number 1234.50, not a string.

Choose a format model that matches the input rather than guessing. A format intended for a decimal point may not interpret text using a decimal comma as intended. Currency symbols can also require a matching format element, such as L for a local currency symbol, or a deliberately designed model for a known currency symbol. When inputs come from different locales or inconsistent sources, standardize them before conversion or handle each known format explicitly.

Convert text to a date or timestamp

For date text, provide a format model to TO_DATE. For instance, if the text is written as year-month-day, use TO_DATE('2026-11-03', 'YYYY-MM-DD'). The format model describes the input’s order and components; it does not change the characters in the input. Using the wrong model can produce an error or an unintended interpretation.

An Oracle DATE stores year, month, day, hour, minute, and second. It does not store fractional seconds or a time zone. If the text includes fractional seconds, use TO_TIMESTAMP, for example TO_TIMESTAMP('2026-11-03 14:05:06.125', 'YYYY-MM-DD HH24:MI:SS.FF3'). For timestamp text that includes a time-zone offset or region, use TO_TIMESTAMP_TZ with a matching format model.

Oracle also supports date literals for fixed dates written directly in SQL: DATE '2026-11-03'. This syntax uses the fixed YYYY-MM-DD form and is useful for a date constant, but it does not convert arbitrary text stored in a column. For a column value, use TO_DATE with a format model that matches that column’s contents.

Keep the data type in mind after conversion. A client tool may display a date in a format controlled by session settings, but that display is not the underlying date value. To present a date as text in a particular format, use TO_CHAR; to turn text into a date, use TO_DATE. These functions do opposite jobs.

Format models and NLS settings

Oracle’s format models describe how input characters map to a value. For numbers, common elements include 9 for a digit position, G for a group separator, and D for a decimal separator. For dates, examples include YYYY for a four-digit year, MM for a month number, DD for a day, HH24 for a 24-hour clock, and MI for minutes.

Some conversion behavior can depend on Oracle’s National Language Support settings, often called NLS settings. For example, the session’s numeric characters determine how decimal and group separators are interpreted when no explicit settings are supplied. Date language settings can affect text month names such as NOV. A query that succeeds in one session can therefore fail or be interpreted differently in another if it relies on implicit defaults.

For predictable results, state the format model and, where relevant, the NLS parameter in the conversion call. When dates use month names, the language can be specified, for example TO_DATE('03-NOV-2026', 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE=English'). For numeric data, pass an explicit NLS_NUMERIC_CHARACTERS setting when the source’s separators are known. This is particularly important for applications that process data from multiple regions.

Handle invalid strings and missing values

A conversion fails when the input cannot be interpreted using the chosen type and format. For example, TO_NUMBER('12x') raises a conversion error rather than silently treating the value as 12. Invalid date text can also raise an error. In a query that processes many rows, one malformed value can prevent the query from returning its results, so data quality and error handling matter.

In Oracle Database 12c Release 2 (12.2) and later, conversion functions support a DEFAULT ... ON CONVERSION ERROR clause. For example, with invalid numeric text in amount_text, TO_NUMBER(amount_text DEFAULT NULL ON CONVERSION ERROR) returns NULL for a value that fails conversion. A fallback can be used instead, such as TO_NUMBER(amount_text DEFAULT 0 ON CONVERSION ERROR), but zero is only appropriate if it genuinely represents the desired business meaning. Treating bad input as zero can hide data problems.

Oracle 12.2 and later also provide VALIDATE_CONVERSION, which can check whether an expression can be converted. For example, VALIDATE_CONVERSION(amount_text AS NUMBER) returns 1 when conversion is valid and 0 when it is not; a null input is treated as valid. A check can help identify rows to review, but the validation format and NLS assumptions should match the eventual conversion. For production data, it is often better to record or correct invalid values than to discard them without notice.

Oracle treats a zero-length character string as NULL in SQL. That means an empty text value is not a numeric zero or a valid date. Decide whether null input should remain null, receive a default, or be flagged for cleanup. Also remember that a fallback in DEFAULT ... ON CONVERSION ERROR handles conversion errors, not every possible error raised while evaluating the input expression.

Avoid implicit conversion in queries

Oracle can sometimes convert one type to another automatically. This is called implicit conversion, and it can make a short expression appear to work. However, relying on it is fragile: the result may depend on session settings, and it can make errors harder to locate. Explicit conversion documents what the query expects and gives you control over the format.

For example, comparing a numeric column with a quoted string such as WHERE order_total = '100' may cause Oracle to convert the string to a number. Prefer a numeric literal, WHERE order_total = 100, or convert a text column deliberately if that is the actual data model. For a date column, compare with a date literal such as DATE '2026-11-03' or a correctly converted value, rather than comparing it to an unformatted string.

Implicit conversion can also affect performance. If Oracle must convert a table column row by row to satisfy a comparison, an index on that column may not be used in the same way as it would for a type-matched comparison. The exact execution plan depends on the query and schema, but matching the types of compared values is a sound default. If a column contains text that represents numbers, consider cleaning the data and storing it as a numeric type where appropriate.

Choose the right conversion pattern

Before writing a conversion, identify the source type, the intended result type, the exact text format, and what should happen when the text is null or malformed. A practical checklist is:

  1. Check the column’s declared type and inspect representative source values.
  2. Choose the matching conversion function: number, date, timestamp, or timestamp with time zone.
  3. Write a format model that matches the text exactly, including separators and time components.
  4. Set relevant NLS parameters when the text’s locale conventions are known.
  5. Choose an explicit policy for invalid and missing values, then test it against real edge cases.

For example, converting a clean column of ISO-style date text can use TO_DATE(date_text, 'YYYY-MM-DD'). A column that sometimes contains unknown needs an additional policy: reject the row, return null for that conversion, or correct the source. Avoid choosing a fallback merely to make a query finish. The correct policy depends on what the data means and how downstream calculations use it.

Frequently Asked Questions

What is the Oracle function to convert a string to a number?
Use TO_NUMBER, such as TO_NUMBER('42'). Add a format model and, if needed, an NLS parameter when the text uses separators or currency conventions that are not the session defaults.

How do I convert a string to a date in Oracle?
Use TO_DATE(text, format_model). For example, convert 2026-11-03 with TO_DATE('2026-11-03', 'YYYY-MM-DD'). The format model must match the order and style of the text.

Why does Oracle raise ORA-01722?
ORA-01722: invalid number commonly means Oracle tried to interpret characters as a number and found text that does not fit, such as letters in a numeric field. Check the source values, format model, and any implicit conversion in comparisons or calculations.

Can I convert an empty string to zero?
Not automatically. In Oracle SQL, an empty character string is treated as null. If the intended rule is to use zero for missing input, state that rule explicitly, for example with NVL after a successful conversion, and make sure zero is a meaningful substitute rather than a way to conceal invalid data.

What is the difference between TO_DATE and TO_CHAR?
TO_DATE converts character text into a date value. TO_CHAR converts a date or number into formatted character text. Use TO_DATE for parsing input and TO_CHAR for presentation.

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.