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

Last modified: February 24, 2023

Was this article helpful?