TO_NUMBER

The Oracle TO_NUMBER function is used to convert a value to a numeric data type (either an integer or a decimal) from various other data types, such as VARCHAR, DATE, or CHAR. Here's a detailed breakdown of the TO_NUMBER function in Oracle:

Syntax:

TO_NUMBER(expression, [format_mask], [nls_param])

Parameters:

1. expression: The value or column that you want to convert into a number. This can be a string, date, or any other compatible data type that can be converted to a number.
2. format_mask (optional): A format model to specify the exact numeric format. The format controls how the conversion handles the input value. If not provided, Oracle will attempt to automatically convert it to the best numeric representation.
• Examples of format masks:
• '9999': converts the number to a 4-digit integer.
• '9999.99': converts the number to a decimal with two decimal places.
• '999G999D99': uses group separators for thousands (G), and decimal points (D).
3. nls_param (optional): NLS (National Language Support) parameters that affect the conversion. It determines the numeric symbols like the decimal separator, grouping separator, etc. For example, you can specify NLS_NUMERIC_CHARACTERS = ',.' to tell Oracle to use a comma for the decimal separator and a period for thousands separator.

Example Usage:

Example 1: Basic Conversion

SELECT TO_NUMBER('12345.67') FROM dual;

Output:

12345.67

This converts the string '12345.67' to a numeric type.

Example 2: Using Format Mask

SELECT TO_NUMBER('1234.56', '9999.99') FROM dual;

Output:

1234.56

In this case, the format mask '9999.99' ensures the number has two decimal places, and it correctly converts the string to a number.

Example 3: Handling Strings with Non-Numeric Characters

SELECT TO_NUMBER('1234abc') FROM dual;

Error:

ORA-01722: invalid number

If the string contains characters that cannot be converted to numbers, Oracle will raise an error.

Example 4: Using NLS Parameters

SELECT TO_NUMBER('1,234.56', '999G999D99', 'NLS_NUMERIC_CHARACTERS = '',.''') FROM dual;

Output:

1234.56

In this case, the NLS_NUMERIC_CHARACTERS parameter is used to define the grouping separator (',') and the decimal separator ('.').

Handling Invalid Numbers:

When using TO_NUMBER, if the input cannot be converted to a valid number (such as containing letters or special characters), an error will be thrown, specifically the ORA-01722: invalid number error. To avoid this, you can use REGEXP_LIKE or CASE statements to check if the input is numeric before attempting the conversion.

For example:

SELECT

  CASE

    WHEN REGEXP_LIKE('123abc', '^\d+(\.\d+)?$')

    THEN TO_NUMBER('123abc')

    ELSE NULL

  END

FROM dual;

This will safely return NULL for non-numeric input.

Common Use Cases:

1. Data Conversion: When dealing with data that's stored as strings or in non-numeric formats but you need to perform mathematical operations.
2. Formatting Numbers: Converting numbers into a specific display format for reporting or exporting.
3. Handling Input Data: Validating and converting user input in forms or queries where data is expected in numeric format but might come as a string.

Performance Considerations:

• Avoid using TO_NUMBER on large datasets where invalid data may cause errors and slow down processing.
• Use CASE or REGEXP_LIKE to filter out invalid values before applying TO_NUMBER to avoid errors.

Summary:

The TO_NUMBER function is a flexible tool for converting various data types into numeric types in Oracle. It's especially useful for handling string-based numbers, performing calculations, and formatting output. The optional format masks and NLS parameters provide additional control over how numbers are parsed and displayed.