TO_NUMBER converts a string to a numeric (decimal) value.
Note
We recommend that you use FM in your format string to suppress padding blanks and zeroes.
For a list of valid formats, see Numeric format strings.
Syntax
to_number(string, format)
Arguments
- string
-
String to be converted. The format must be a literal value.
- format
-
The second argument is a format string that indicates how the character string should be parsed to create the numeric value. For example, the format
'FM99D999'specifies that the string to be converted consists of five digits with the decimal point in the third position. For example,to_number('12.345','FM99D999')returns12.345as a numeric value. For a list of valid formats, see Numeric format strings.
Return type
TO_NUMBER returns a DECIMAL number.
If the conversion to format fails, then an error is returned.
Examples
The following example converts the string 12,454.8- to a number:
select to_number('12,454.8-', 'FM99G999D9S');
to_number
-----------
-12454.8
The following example converts the string $ 12,454.88 to a number:
select to_number('$ 12,454.88', 'FML99G999D99');
to_number
-----------
12454.88
The following example converts the string $ 2,012,454.88 to a number:
select to_number('$ 2,012,454.88', 'FML9,999,999.99');
to_number
-----------
2012454.88
