Important

New database features are no longer connected to engine versions; features are now enabled independently during scheduled update windows, and “major” and “minor” releases no longer exist in Helios. Please visit the release notes to view the latest features available in your database clusters.

TO_NUMBER

Syntax

SQL

TO_NUMBER ( value [, format_string ] )

Arguments

value: Any CHAR or VARCHAR type value.
format_string: The format string used to convert the input value.

Return Type

A decimal value.

Remarks

If the format_string is 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.

SQL

SELECT TO_NUMBER('9876.34') AS "Output";

+---------+
| Output  |
+---------+
| 9876.34 |
+---------+

SQL

SELECT TO_NUMBER('9876.34', '999999.99') AS "Output";

+---------+
| Output  |
+---------+
| 9876.34 |
+---------+

SQL

SELECT TO_NUMBER('5645342', '9999999') AS "Output";

+---------+
|  Output |
+---------+
| 5645342 |
+---------+

SQL

SELECT TO_NUMBER('1,234.56789', '999,999,999.99999') AS "Output";

+------------+
| Output     |
+------------+
| 1234.56789 |
+------------+

SQL

SELECT TO_NUMBER('$78.12', '$99.99') AS "Output";

+--------+
| Output |
+--------+
|  78.12 |
+--------+

SQL

SELECT TO_NUMBER('123,456,789', '999,999,999') AS "Output";

+-----------+
| Output    |
+-----------+
| 123456789 |
+-----------+

SQL

SELECT TO_NUMBER('1.01234567890000000000E010', '9.99999999999999999999EEEE') AS "Output";

+---------------+
| Output        |
+---------------+
| 10123456789.0 |
+---------------+