Oracle PL/SQL Tutorial/Conversion Functions/TO NUMBER

Converts the string -$12,345.67 to a number, passing the format string $99,999.99 to TO_NUMBER()

SQL>
SQL> SELECT TO_NUMBER("-$12,345.67", "$99,999.99") FROM dual;
TO_NUMBER("-$12,345.67","$99,999.99")
-------------------------------------
                            -12345.67
SQL>
SQL>

Convert the string 97.13 to a number using TO_NUMBER() and then adds 25.5 to that number

SQL>
SQL> SELECT TO_NUMBER("97.13") + 25.5 FROM dual;
TO_NUMBER("97.13")+25.5
-----------------------
                 122.63
SQL>

to_number("123")

SQL>
SQL>
SQL> select 123
  2  ,      to_char(123)
  3  ,      to_char(123,"$09999.99")
  4  ,      to_number("123")
  5  from   dual;
       123 TO_ TO_CHAR(12 TO_NUMBER("123")
---------- --- ---------- ----------------
       123 123  $00123.00              123
SQL>

TO_NUMBER(x [, format]) converts x to a number.

The TO_NUMBER function converts a character string of type CHAR or VARCHAR2 into a number.

The Syntax for the TO_NUMBER Function

TO_NUMBER(character_string, format, NLS_Params)

After the format mask are several possible NLS parameters:

1. NLS_NUMERIC_CHARACTERS -- Specifies characters to use for group separators and the decimal point.
2. NLS_CURRENCY -- Specifies the local currency.
3. NLS_ISO_CURRENCY -- Character(s) to represent the ISO currency symbol.

You can provide an optional format string to indicate the format of x.

Parameter Format Examples 9 999 0 0999 9990 . 999.99 , 9,999 $ $999 B B9.99 C C999 D 9D99 EEEE 9.99EEEE FM FM90.9 G 9G999 L L999 MI 999MI PR 999PR RN rn RN rn S S999 999S TM TM U U999 V 99V99 X XXXX

The following example converts the string 97.13 to a number using TO_NUMBER():

SQL>
SQL> SELECT TO_NUMBER("97.13") FROM dual;
TO_NUMBER("97.13")
------------------
             97.13
SQL>