Oracle SQL: the Essential Reference

TO_NUMBER

TO_NUMBER(string [,fmt[, 'nlsparams']])

Converts a character-based representation of a numeric value to a NUMBER value.

Parameters

string

Specifies a character string containing the character-based representation that you want to convert.

fmt

Specifies a number format specification made up of the elements shown in Table 5-5.

nlsparams

Specifies a character string made up of one or more of the following elements, allowing you to specify various NLS characteristics for the result:

NLS_NUMERIC_CHARACTERS='' dg''

Allows you to specify the characters to use for decimal points and group separators. The d character becomes the decimal point and the g character becomes the group separator. For this to take effect, you must use D and G in your format specification to mark the location of decimal point and group separator, respectively.

NLS_CURRENCY='' text''

Allows you to specify the currency symbol (up to 10 characters long) to use in place of any L characters contained in the format specification.

NLS_ISO_CURRENCY='' territory''

Allows you to specify the NLS territory whose currency symbol you want to use in place of any C characters contained in the format specification.

Examples

SQL> VARIABLE x NUMBER
SQL> SELECT TO_NUMBER('123.45') INTO :x FROM dual;

TO_NUMBER('123.45')
-------------------
             123.45

SQL> SELECT TO_NUMBER('$123,456.78','$999,999.99') INTO :x FROM dual;

TO_NUMBER('$123,456.78','$999,999.99')
--------------------------------------
                             123456.78