TO_
On this page
Converts a CHAR or VARCHAR type value to a DECIMAL value.
Syntax
TO_NUMBER ( value [, format_string ] )
Arguments
-
value: AnyCHARorVARCHARtype value. -
format_: The format string used to convert the inputstring value.
Return Type
A decimal value.
Remarks
-
If the
format_is not specified, the input argument is converted to a numeric value.string -
The return value will have a precision of
DECIMAL(65,15).
The following table describes the elements in a format_.
|
Element |
Description |
Example |
|---|---|---|
|
, (comma) |
Specifies the position of commas in the input value. |
9,999,999 |
|
. |
Specifies the position of the decimals. |
9,999. |
|
$ (Dollar sign) |
Specifies that the input value has a leading $ (Dollar) sign. |
$999 |
|
EEEE |
Specifies that the input value is in scientific notation. |
9. |
|
9 |
Specifies the number of digits, with a leading - (minus) if the number is negative. |
99999 |
|
S |
Depending on it its placement, it identifies a number as positive or negative with the use of + (plus) or - (minus) sign. |
S999 or 999S |
Example
The following examples demonstrate the use of TO_ function.
SELECT TO_NUMBER('9876.34') AS "Output";
+---------+
| Output |
+---------+
| 9876.34 |
+---------+SELECT TO_NUMBER('9876.34', '999999.99') AS "Output";
+---------+
| Output |
+---------+
| 9876.34 |
+---------+SELECT TO_NUMBER('5645342', '9999999') AS "Output";
+---------+
| Output |
+---------+
| 5645342 |
+---------+SELECT TO_NUMBER('1,234.56789', '999,999,999.99999') AS "Output";
+------------+
| Output |
+------------+
| 1234.56789 |
+------------+SELECT TO_NUMBER('$78.12', '$99.99') AS "Output";
+--------+
| Output |
+--------+
| 78.12 |
+--------+SELECT TO_NUMBER('123,456,789', '999,999,999') AS "Output";
+-----------+
| Output |
+-----------+
| 123456789 |
+-----------+SELECT TO_NUMBER('1.01234567890000000000E010', '9.99999999999999999999EEEE') AS "Output";
+---------------+
| Output |
+---------------+
| 10123456789.0 |
+---------------+Last modified: February 24, 2023