Important

The SingleStore 9.1 release candidate (RC) is available now. It will be promoted to general availability (GA) soon and at that time it will be renamed to SingleStore 10.

In the interim, SingleStore 9.1 RC can be used to preview, evaluate, and provide feedback on the new and upcoming features in SingleStore 10, while SingleStore 9.0 is recommended for production workloads.

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