Join the SingleStore Community Today
Get expert advice, develop skills, and connect with others.

TO_NUMBER

Converts a CHAR or VARCHAR type value to a DECIMAL value.

Syntax

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.

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