Oracle TO_NUMBER Function Usage, Tips & Examples

The Oracle TO_NUMBER function is one of the most common number manipulation functions in Oracle. In this article and video, I’ll explain how to use the TO_NUMBER function.

In this article, you’ll learn:

  • What the TO_NUMBER function does
  • The syntax and parameters of the TO_NUMBER function
  • Several examples of queries with the TO_NUMBER function
  • Answers to some FAQ on this function


What Is The Oracle TO_NUMBER Function?

The Oracle TO_NUMBER function is used to convert a text value to a number value.

It works similar to the TO_DATE and TO_CHAR functions but converts the values to a number.

The function takes many different data types:

  • CHAR

The TO_NUMBER returns a value in the NUMBER data type.

Also, the number will be rounded to the specified number of digits, which may cause undesired effects.



The syntax of the Oracle TO_NUMBER function is:

TO_NUMBER( input_value, [format_mask], [nls_parameter] )



The parameters of the TO_NUMBER function are:

  • input_value (mandatory): This is the value that is to be converted to a number. It can be one of the many data types mentioned earlier but is most commonly provided as a string.
  • format_mask (optional): This is the format that the output value should be displayed as. It must be a valid number format, which is explained below.
  • nls_parameter (optional): This value is used to determine how the output value is displayed, such as currency symbols and numeric characters.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:


Oracle TO_NUMBER Format Mask

The Oracle TO_NUMBER format mask for this function must be a number format. The possible values for number formats you can use are:

Format Explanation
9 Single number character. Removes leading and trailing zeroes. Includes a leading hyphen (-) for negative numbers.
0 Single number character. Includes leading and trailing zeroes.
FM Indicates that the format should suppress all leading and trailing blanks
$ Include a leading $ sign
B Changes the ‘0’ format and replace leading 0’s with blank spaces.
S Include both positive (+) and negative (-) signs
PR Indicate sign of the number with angle brackets (<>)
MI Indicate the minus sign
RN or rn Displays the number in Roman numerals (upper or lower case)
D Indicates the position of the decimal point (.)
G Adds a group separator (often a comma) in a position
, Adds a comma in a position
. Adds a period in a position
C Adds the ISO currency symbol
L Adds the local currency symbol
EEEE Displays the value using scientific notation
TM Displays the Text Minimum in TM9(the default) or TME (Scientific notation)
U Displays in the specified position the Euro (or other) dual currency symbol.
V Displays the value multiplied by 10n, where n is the number of 9’s after the letter V. The value may be rounded up if necessary
X Returns the hexadecimal value of the specified number of digits.

If the format mask is not specified, it uses the period character (.) as the decimal place and doesn’t recognise any other symbol.

Want to see guides like this for all other Oracle functions? Check out this page here that lists all SQL functions along with links to their guides.



This parameter is used to add extra information to the function in order for the value to be displayed. It works similar to the TO_CHAR function but has a few differences.

The expression added here will represent the thousand group marker, decimal marker, and the currency symbols to be used. The expression for this parameter can contain one or more of the following parameters, separated by commas:

  • NLS_CURRENCY symbol
  • NLS_ISO_CURRENCY territory

With these statements:

  • symbol represents the local currency symbol and must be less than or equal to 10 characters
  • territory is a text expression that identifies the territory whose ISO currency symbol is used
  • dg represents two characters, which are single-byte characters, used for the decimal marker and thousands group marker.


Examples of the TO_NUMBER Function

Here are some examples of the TO_NUMBER function. I find that examples are the best way for me to learn about code, even with the explanation above.

These examples all use the DUAL table, which is a table in Oracle that allows you to display data in a SELECT statement without needing to create or select from a table in your database. Related: The Oracle DUAL Table.

Example 1 – Default Format

This example converts a simple string to a number value.





Example 2 – Specified Format with Decimals

This example converts the same string into a number, using the specified format.

TO_NUMBER('5467.12', '999999.99')




Example 3 – Format No Decimals

This example converts a number which has no decimal places in it.

TO_NUMBER('4687841', '9999999')




Example 4 – Format with Currency

This example treats the dollar sign as a currency figure and converts it to a number.

SELECT TO_NUMBER('$65.169', 'L99.999')




Example 5 – Format with Thousand Group Markers

This example handles the commas that are used as thousand group markers.

TO_NUMBER('123,456,789', '999,999,999')




Example 6 – Uses NLS Parameter

This example takes a complicated string, determines that it is in a certain format, and specifies the numeric characters to use

TO_NUMBER('$17 218,00', 'L999G999D00',' NLS_NUMERIC_CHARACTERS='', ''')




TO_NUMBER Frequently Asked Questions

Why Does TO_NUMBER Give an Invalid Number Error?

Sometimes you might get the following error when attempting to use the TO_NUMBER function:

ORA-01722: invalid number.

This is because the value inside the TO_NUMBER field is not a number, and it needs to be.

This could be happening for several reasons:

  • The data type of the column you’re providing it is not a number.
  • The data type of the column you’re providing it is not a number, and it contains numbers stored as strings, but a value was found that was not numeric
  • The data in the column contains spaces
  • A conversion is happening in the WHERE clause which is causing the data to be converted to another type.

So, to fix the to_number invalid number error:

  • If you’re doing an INSERT INTO, you’ll need to find out which column and value is causing the issue. Try a SELECT DISTINCT on the column to see where the issue is.
  • If you’re doing an UPDATE, it could be because the data type is not right in the field you’re updating from.
  • If you’re doing a SELECT, it could also be because the data type is not what is expected. You’ll need to find what data is causing the issue to see why your SELECT TO_NUMBER is failing.


Can I Use Oracle TO_NUMBER With Dates?

Yes and no. You can’t supply a date value as the parameter directly:


But, you can convert it to a string value and then use that as a parameter, to use Oracle TO_NUMBER with a date:



Can I Keep The Leading Zeroes Using Oracle TO_NUMBER?

Well, Oracle number types don’t have leading zeroes. Leading zeroes are only for string types (such as VARCHAR2).

So, if you use Oracle TO_NUMBER for leading zeroes, it will remove the zeroes from the original value.


Possible Errors Using the TO_NUMBER Function

Some of the errors you might get when using the Oracle TO_NUMBER function are:

  • ORA-01722: invalid number. This happens when the input value does not represent a number. It can also happen when the string supplied as the format mask is not correct.


Similar Functions

Some functions which are similar to the TO_DATE function are:

  • TO_DATE – converts a string value to a DATE type.
  • TO_CHAR – converts a number or date value to a string type.
  • CAST – used for many types, including dates

You can find a full list of Oracle functions here.

While you’re here, if you want an easy-to-use list of the main features in Oracle SQL, get my SQL Cheat Sheet here:

9 thoughts on “Oracle TO_NUMBER Function Usage, Tips & Examples”

  1. I am trying to search for a number like 61 123456 in a range. This is stored as varchar in db.
    i did something like this TO_NUMBER(’61 123456′) BETWEEN

    1. You might need to use the same conversion method for both your input value (61 123456) and the database columns.
      Could you try to select the values first, and then see what they display as?
      FROM yourtable;

      This way you can see what the values look like and see if your functions show you what you need.

  2. Iam trying use to_numer to convert varchr2 like ‘five’ or ‘four’ , how its work ?
    to_number(‘four’,9′) from dual;

    1. The default format may be used, and if your input is in this format it will be OK, but if it’s in a different format, then you may get an error.

      1. Prathmesh Gonjare

        What ia the significance of the to_number function as we passed only the numeric value and we get same numeric value as output that we passed in input string????

  3. select EMPVISA_EMP_CODE,
    reverse (EMPVISA_EMP_CODE)
    order by EMPVISA_EMP_CODE desc;

    When i am using order by clause in this query it shows ” INVALID NUMBER ” error. Without Order by clause it works fine. how can i use Order by clause here can you help me ?

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.