Important

Self-managed SingleStore will soon transition from version 9.1 RC to version 10. This new semantic versioning scheme will provide SingleStore with finer control over engine and feature releases that were not possible with the current versioning scheme.

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 prior to its general availability. Ahead of this transition, SingleStore 9.0 is recommended for production workloads, which can later be upgraded to SingleStore 10.

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