TO_NUMBER

Converts a string value to DOUBLE PRECISION.

Behavior type

Syntax

TO_NUMBER ( expression, [ pattern ] )

Parameters

expression: Specifies the string value to convert, either CHAR or VARCHAR.
pattern: A string value, either CHAR or VARCHAR, that specifies an output pattern string using one of the supported Template patterns for numeric formatting. If you omit this parameter, TO_NUMBER returns a floating point.

Notes

To use a double quote character in the output, precede it with a double backslash. This is necessary because the backslash already has a special meaning in a string constant. For example: '\\"YYYY Month\\"'

Note

To convert a date string to a numeric value, use the appropriate date/time function, such as EXTRACT.

Examples

=> SELECT TO_NUMBER('MCML', 'rn');
TO_NUMBER
-----------
1950
(1 row)

It the pattern parameter is omitted, the function returns a floating point. For example:

=> SELECT TO_NUMBER('-123.456e-01');
 TO_NUMBER
-----------
  -12.3456