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.
On this page
Converts a CHAR
or VARCHAR
type value to a DECIMAL
value.
Syntax
TO_NUMBER ( value [, format_string ] )
Arguments
-
value
: AnyCHAR
orVARCHAR
type value. -
format_
: The format string used to convert the inputstring value
.
Return Type
A decimal value.
Remarks
-
If the
format_
is not specified, the input argument is converted to a numeric value.string -
The return value will have a precision of
DECIMAL(65,15)
.
The following table describes the elements in a format_
.
Element |
Description |
Example |
---|---|---|
, (comma) |
Specifies the position of commas in the input value. |
9,999,999 |
. |
Specifies the position of the decimals. |
9,999. |
$ (Dollar sign) |
Specifies that the input value has a leading $ (Dollar) sign. |
$999 |
EEEE |
Specifies that the input value is in scientific notation. |
9. |
9 |
Specifies the number of digits, with a leading - (minus) if the number is negative. |
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_
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 |
+---------------+
Last modified: February 24, 2023