TO_NUMBER

Syntax

Description of the illustration to_number.gif

Purpose

TO_NUMBER converts expr to a value of NUMBER data type. The expr can be a BINARY_DOUBLE value or a value of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 data type containing a number in the format specified by the optional format model fmt.

You can specify an expr of BINARY_FLOAT. However, it makes no sense to do so because a float can be interpreted only by its internal presentation.

This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion.

See Also:

"Data Type Comparison Rules" for more information.

Examples

The following examples convert character string data into a number:

UPDATE employees SET salary = salary + 
   TO_NUMBER('100.00', '9G999D99')
   WHERE last_name = 'Perkins';

The 'nlsparam' argument in this function has the same purpose as it does in the TO_CHAR function for number conversions. Refer to TO_CHAR (number) for more information.

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

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