TO_NUMBER

Purpose

This function converts a string into a number.

Usage Notes

• The format has no influence on the value, but simply its representation.
• If a format is specified, the corresponding string may only contain digits as well as the characters - plus, minus, NLS_NUMERIC_CHARACTERS, decimal point (decimal separator), and comma (group separator). However, plus and minus may only be used at the beginning of the string.
• The format for every digit in the string must contain format element nine or zero at least once. For information, refer to Numeric format models.
  If the string contains a decimal separator, the format must also contain the corresponding decimal separator element (D or '.').
• If the data type of parameter string is no string, then the value is implicitly converted.
• The data type of the result of this function is dependent on the format, but typically a DECIMAL type. If no format is specified, the result type is DECIMAL(1,0) in case of a boolean input parameter, and DOUBLE in any other case.

Example

SELECT TO_NUMBER('+123') TO_NUMBER1,  
       TO_NUMBER('-123.45', '99999.999') TO_NUMBER2;