SQL Pocket Guide, 3rd Edition

Numeric Conversions: Oracle

Use the following functions in Oracle to convert to and from the supported numeric types:

TO_NUMBER(string, format)
TO_BINARY_DOUBLE(string, format)
TO_BINARY_FLOAT(string, format)
TO_CHAR(number, format)
TO_BINARY_DOUBLE(number)
TO_BINARY_FLOAT(number)
TO_NUMBER(number)

Use TO_NUMBER and TO_CHAR (the only two functions available prior to Oracle Database 10g) to convert between NUMBER and VARCHAR2 (Table 1-9 lists the available numeric format elements):

SELECT
   TO_CHAR(1234.56,'C9G999D99') to_char,
   TO_NUMBER('1,234.56','9G999D99') from_char,
   TO_CHAR(123,'999V99') v_example
FROM dual;

TO_CHAR          FROM_CHAR              V_EXAMPLE
---------------- ---------------------- ---------
     USD1,234.56 1234.56                 12300

Use TO_BINARY_FLOAT and TO_BINARY_DOUBLE to convert to the new 32- and 64-bit IEEE 754 floating-point types added in Oracle Database 10g. Also use these functions to convert values from one numeric type to another.

Table 1-9. Oracle’s numeric format elements

Element	Description
$	Prefix: dollar sign ($).
, (comma)	Location of comma. Consider G instead.
. (period)	Location of period. Consider D instead.
0	Significant digit. Leading zeros.
9	Significant digit. Leading blanks.
B	Prefix: returns zero as blanks.
C	Location of ISO currency symbol.
D	Location of decimal point.
EEEE	Suffix: use scientific notation.
FM	Prefix: removes leading/trailing blanks.
G	Location of group separator.
L	Location of local currency symbol.
MI	Suffix: trailing minus (–) sign.
PR	Suffix: angle brackets (< and >) around negative values. ...