TO_NUMBER
TO_NUMBER(x [, format], [ nls_language ]) converts x to a NUMBER.
- x is the string that will be converted to a number.
- format, optional, is the format that will be used to convert x to a number.
- nls_language, optional, is the nls language used to convert x to a number.
Number Format Elements:
| Element | Example | Description |
|---|---|---|
| , . | 1,234.56 | Commas and decimal points |
| $ | $123.45 | Leading dollar sign. |
| 0 | 0012.34 | Leading or trailing 0. |
| 9 | 123 | Any digit. |
| B | B123 | Leading blank for integers. |
| C | C123 | The ISO currency symbol defined in the NLS_ISO_CURRENCY parameter. |
| D | 123D99 | The current decimal character defined in the NLS_NUMERIC_CHARACTERS parameter. The default value is a period. |
| EEEE | 1.2EEE | Returns a value in scientific notation. |
| G | 9G123 | Returns the group separator (e.g., a comma). |
| L | L123 | Returns the local currency symbol. |
| MI | 123MI | negative value with trailing minus sign; returns positive value with a trailing blank. |
| PR | 123PR | The negative values in angle brackets. |
| RN | I | values in Roman numerals, uppercase. |
| rn | i | values in Roman numerals, lowercase. |
| S (prefix) | S1234 | negative values with a leading minus sign, positive values with a leading positive sign. |
| S (suffix) | 1234S | negative values with a trailing minus sign, positive values with a trailing positive sign. |
| TM | TM | The text minimum number format model returns the smallest number of characters possible. |
| U | U123 | the Euro currency symbol or the NLS_DUAL_CURRENCY parameter. |
| V | 123V99 | a value multiplied by 10n, where n is the number of 9s after the V. |
| X | XXXX | the hexadecimal value. |
SQL> select to_number('123.45') from dual;
TO_NUMBER('123.45')
-------------------
123.45
SQL> select to_number('123.45', '9999.99') from dual;
TO_NUMBER('123.45','9999.99')
-----------------------------
123.45
SQL> select to_number('12', '99') from dual;
TO_NUMBER('12','99')
--------------------
12
SELECT TO_NUMBER('$12,123.23','$999,999.99') FROM DUAL;
SQL>
SQL>
SQL>
SQL>
Do calculation after the conversion
SQL> select to_number('123.45') + 2 from dual;
TO_NUMBER('123.45')+2
---------------------
125.45
SQL> SELECT TO_NUMBER('-$12,345.67', '$99,999.99') FROM dual;
TO_NUMBER('-$12,345.67','$99,999.99')
-------------------------------------
-12345.67
SQL>