TO_NUMBER

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

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 |
+---------------+