Outdated Version
You are viewing an older version of this section. View current production version.
TO_NUMBER
Converts a CHAR or VARCHAR type value to a DECIMAL value.
Syntax
TO_NUMBER ( value [, format_string ] )
Arguments
value: AnyCHARorVARCHARtype value.format_string: The format string used to convert the inputvalue.
Return Type
A decimal value.
Remarks
- If the
format_stringis not specified, the input argument is converted to a numeric value. - The return value will have a precision of
DECIMAL(65,15).
The following table describes the elements in a format_string.
| Element | Description | Example |
|---|---|---|
| , (comma) | Specifies the position of commas in the input value. While multiple commas can be specified in a format, it cannot be used at the start or to the right of a decimal in a format string. | 9,999,999 |
| . (period) | Specifies the position of the decimals. Only one decimal can be specified in a format string. | 9,999.99 |
| $ (Dollar sign) | Specifies that the input value has a leading $ (Dollar) sign. | $999 |
| EEEE | Specifies that the input value is in scientific notation. | 9.9EEE |
| 9 | Specifies the number of digits, with a leading - (minus) if the number is negative. The output will have no leading zeroes, except for a zero value. | 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_NUMBER 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 |
+---------------+
