Oracle TO_NUMBER() Function

Oracle TO_NUMBER() is a built-in function that converts the given argument to a value of data type NUMBER.

Oracle `TO_NUMBER()` Syntax

Here is the syntax of the Oracle TO_NUMBER() function:

TO_NUMBER(expr [ DEFAULT return_value ON CONVERSION ERROR ]
  [, fmt [, 'nlsparam' ] ])

Parameters

expr: Required. It can be any expression that evaluates to a character string of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2, a numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null.
DEFAULT return_value ON CONVERSION ERROR: Optional. It allows you to specify the value to return when a conversion error occurs. return_value can be an expression or a bind variable and must evaluate to a character string of type CHAR, VARCHAR2, NCHAR, or NVARCHAR2, a numeric value of type NUMBER, BINARY_FLOAT, or BINARY_DOUBLE, or null. The function converts return_value to BINARY_DOUBLE in the same way as it converts expr to BINARY_DOUBLE. If return_value cannot be converted to BINARY_DOUBLE, the function returns an error.
fmt: Optional. A format model.
'nlsparam': Optional. You can set this parameter using the 'NLS_DATE_LANGUAGE = language' form, where language is a language name.

Return Value

The Oracle TO_NUMBER() function returns a value of data type NUMBER.

If expr is a NUMBER, the function returns expr. If the evaluation of expr results in null, the function returns null. Otherwise, the function converts expr to a NUMBER value.

If you specify expr as a data type of CHAR, VARCHAR2, NCHAR, or NVARCHAR2, you can optionally specify a format model fmt.
If you specify expr as a data type of BINARY_FLOAT or BINARY_DOUBLE, you cannot specify a format model because floating-point numbers can only be interpreted by their internal representation.

This function does not directly support CLOB data. However, a CLOB can be passed as an argument using implicit data conversion.

If any of the arguments is NULL, TO_NUMBER() returns NULL.

Oracle `TO_NUMBER()` Examples

Here are some examples that demonstrate the usage of the Oracle TO_NUMBER() function.

Basic Usage

SELECT TO_NUMBER('100.00')
FROM dual;

输出：

   TO_NUMBER('100.00')
______________________
                   100

Formatting

For some numbers with thousands separators, Oracle reports an error if you try to convert them directly to a number:

SELECT TO_NUMBER('1,234,567.89')
FROM dual;

输出：

SQL Error: ORA-01722: invalid number
01722. 00000 -  "invalid number"
*Cause:    The specified number was invalid.
*Action:   Specify a valid number.

The Oracle TO_NUMBER() function allows you to specify the format of the input number:

SELECT TO_NUMBER('1,234,567.89', '9G999G999D99')
FROM dual;

输出：

   TO_NUMBER('1,234,567.89','9G999G999D99')
___________________________________________
                                 1234567.89

NULL Parameter

If any parameter is NULL, TO_NUMBER() will return NULL.

SET NULL 'NULL';
SELECT
    TO_NUMBER(NULL)
FROM dual;

输出：

   TO_NUMBER(NULL)
__________________
              NULL

In this example, we use the statement SET NULL 'NULL'; to display the NULL value as the string 'NULL'.

Conclusion

Oracle TO_NUMBER() is a built-in function that converts the given parameter to a value of NUMBER data type.

Oracle TO_NUMBER() Syntax