Oracle TO_NUMBER Function: A Simple Guide

The TO_NUMBER function in Oracle SQL is an essential conversion function. Its primary job is to convert a text string (like a VARCHAR2 or CHAR) into an actual NUMBER data type.

This is the direct opposite of the TO_CHAR(number) function. You must use TO_NUMBER any time you need to perform mathematical calculations (like SUM, AVG, +, -) on data that is stored as text.

What is the TO_NUMBER Function in Oracle?

The TO_NUMBER(expr, 'format') function "translates" a character string into a NUMBER. To do this, you can provide a "format model" (fmt) that tells Oracle exactly how to read the string, especially if it contains characters like currency symbols ($), group separators (,), or different decimal separators (.).

A key modern feature of this function is the optional DEFAULT ... ON CONVERSION ERROR clause. This allows you to provide a fallback value (like 0 or NULL) if the conversion fails, which prevents your entire query from stopping with an error on "bad" data.

TO_NUMBER Function Syntax

The syntax for TO_NUMBER is:

TO_NUMBER(expr [DEFAULT return_value ON CONVERSION ERROR]
        [, fmt]
        [, 'nlsparam'])

Let's break that down:

• expr: The string or value you want to convert (e.g., '$1,250.00').
• DEFAULT return_value ON CONVERSION ERROR (Optional): This is a powerful safety feature. It provides a default value to return if the expr string cannot be converted to a number.
• [fmt] (Optional, but recommended): The "format model" that tells Oracle how to read the string (e.g., 'L9G999D99').
• [nlsparam] (Optional): A string that defines special characters for currency (L), decimal (D), and group (G) separators.

Oracle TO_NUMBER Function Examples

Here are two practical examples of how to use TO_NUMBER.

Example 1: Converting a Formatted Currency String using TO_NUMBER

This example shows how to convert a complex text string that includes a currency name (AusDollars) and a non-standard decimal separator (,) into a clean number. We must tell the function what the currency symbol and decimal/group separators are.

• L in the format matches NLS_CURRENCY
• D in the format matches the first character in NLS_NUMERIC_CHARACTERS (the comma)
• G in the format matches the second character in NLS_NUMERIC_CHARACTERS (the period)

Query:

SELECT
  TO_NUMBER(
    '-AusDollars100,00',
    'L9G999D99',
    'NLS_NUMERIC_CHARACTERS = '',.''
     NLS_CURRENCY = ''AusDollars''
   ') AS "Amount"
FROM DUAL;

Result: (The string is successfully converted to the number -100)

    Amount
----------
      -100

Example 2: Safely Handling Bad Data with TO_NUMBER

This is a very common and powerful use case. Imagine you have a column with a mix of numbers and "bad" data. The string '2,00' will fail conversion in a standard US/English session because the comma is in the wrong place (it expects a decimal .).

Instead of letting the query error, we can tell it to return 0 instead.

Query:

SELECT
  TO_NUMBER(
    '2,00' DEFAULT 0 ON CONVERSION ERROR
  ) AS "Value"
FROM DUAL;

Result: (Instead of an ORA-01722: invalid number error, the query safely returns 0.)

     Value
----------
         0