Oracle TO_NUMBER Function Usage, Tips & Examples

calendar Dec 30, 2014 · 7 min read · sql-functions  ·
Share on: linkedin copy

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:

  • BINARY_FLOAT
  • BINARY_DOUBLE
  • CHAR
  • VARCHAR2
  • NCHAR
  • NVARCHAR2

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.

Syntax

The syntax of the Oracle TO_NUMBER function is:

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

Parameters

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.

NLS_PARAMETER Explanation

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
  • NLS_NUMERIC_CHARACTERS dg

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.

1SELECT
2TO_NUMBER('5467.12')
3FROM DUAL;

Result:

15467.12

Example 2 - Specified Format with Decimals

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

1SELECT
2TO_NUMBER('5467.12', '999999.99')
3FROM DUAL;

Result:

15467.12

Example 3 - Format No Decimals

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

1SELECT
2TO_NUMBER('4687841', '9999999')
3FROM DUAL;

Result:

14687841

Example 4 - Format with Currency

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

1SELECT TO_NUMBER('$65.169', 'L99.999')
2FROM DUAL;

Result:

165.169

Example 5 - Format with Thousand Group Markers

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

1SELECT
2TO_NUMBER('123,456,789', '999,999,999')
3FROM DUAL;

Result:

1123456789

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

1SELECT
2TO_NUMBER('$17 218,00', 'L999G999D00',' NLS_NUMERIC_CHARACTERS='', ''')
3FROM DUAL;

Result:

117218

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:

1TO_NUMBER(datevalue)

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

1TO_NUMBER(TO_CHAR(datevalue))

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:

comments powered by Disqus