Purpose
TO_NUMBER() converts strings of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE type to values of the NUMBER type.
Syntax
TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])
Parameters
| Parameter | Description |
|---|---|
| expr | Values of the CHAR, VARCHAR2, NCHAR, NVARCHAR2, BINARY_FLOAT, or BINARY_DOUBLE data type. |
| fmt | Optional. The format model of expr. Note expr must be within the range of fmt, such as TO_NUMBER('$123456.78','$999999999.999'). |
| nlsparam | Optional. Specifies the language of the data to be returned. |
Return type
The return type is NUMBER.
Examples
Convert the
0123456string to a numeric value.obclient> SELECT TO_NUMBER('0123456') FROM DUAL; +----------------------+ | TO_NUMBER('0123456') | +----------------------+ | 123456 | +----------------------+ 1 row in setConvert the hexadecimal number
16fto a decimal number.obclient> SELECT TO_NUMBER('16f','xxx') FROM DUAL; +------------------------+ | TO_NUMBER('16F','XXX') | +------------------------+ | 367 | +------------------------+ 1 row in set