Understanding the ORA-01722 Error and Converting VARCHAR2 to NUMBER

ORA-01722 is an error message that occurs when attempting to convert a string that contains non-numeric characters to a number. In this article, we will explore the cause of this error and provide solutions for converting VARCHAR2 columns to NUMBER.

The Problem with VARCHAR2 Columns

The issue arises when trying to transfer data from a VARCHAR2 column in the source table to a NUMBER column in the destination table. The problem is that the VARCHAR2 column contains leading zeros, commas, or other non-numeric characters that prevent Oracle from correctly parsing the value as an integer.

Common Causes of ORA-01722 Errors

There are several reasons why you may encounter ORA-01722 errors when converting VARCHAR2 columns to NUMBER:

• Leading zeros: If a column contains values with leading zeros, such as 001, Oracle will not recognize these as valid numbers.
• Commas: Comma-separated values can also cause issues, especially if the comma is used as a thousand separator or in other contexts where it has no special meaning.
• Other non-numeric characters: Characters like dashes (-), periods (.), or spaces ( ) can prevent Oracle from correctly parsing the value as an integer.

Solution 1: Using the nls_numeric_characters Parameter

To resolve ORA-01722 errors, you need to adjust the nls_numeric_characters parameter. This parameter determines which characters are considered valid numeric digits in a given locale.

By default, Oracle considers commas (,) and periods (. ) as valid numeric characters. To convert VARCHAR2 columns to NUMBER, you must set this parameter to exclude these characters.

Here is an example of how to do it using the SELECT statement:

SELECT TO_NUMBER(column_a, '9999999999D9999999999999999999999',
  'nls_numeric_characters= ''.,''') as my_numbers
FROM table_name;

In this code:

• We use the TO_NUMBER() function to convert the column value to a number.
• The first argument is the format mask used to specify the conversion pattern. In this case, we use '9999999999D9999999999999999999999999', which means “parse up to 20 digits after the decimal point”.
• The second argument specifies the nls_numeric_characters parameter and excludes commas (,) and periods (. ) from being considered valid numeric digits.

Solution 2: Using a Common Table Expression (CTE)

Another approach is to use a CTE to first transform the data into a format that Oracle can parse as numbers. Here’s an example of how you could do this:

WITH my_numbers (column_a)
AS (
    SELECT ',00100000004749745' FROM DUAL
    UNION ALL
    SELECT ',100000001490116' FROM DUAL
    UNION ALL
    SELECT ',125' FROM DUAL
    UNION ALL
    SELECT ',200000002980232' FROM DUAL
    UNION ALL
    SELECT ',25' FROM DUAL
    UNION ALL
    SELECT ',439999997615814' FROM DUAL
    UNION ALL
    SELECT ',5' FROM DUAL
    UNION ALL
    SELECT '0' FROM DUAL
    UNION ALL
    SELECT '1' FROM DUAL
    UNION ALL
    SELECT '1,10000002384186' FROM DUAL
    UNION ALL
    SELECT '1,5' FROM DUAL
    UNION ALL
    SELECT '100' FROM DUAL
    UNION ALL
    SELECT '2,1800000667572' FROM DUAL
    UNION ALL
    SELECT '3' FROM DUAL
    UNION ALL
    SELECT '3,29999995231628' FROM DUAL
    UNION ALL
    SELECT '96' FROM DUAL
    UNION ALL
    SELECT '999' FROM DUAL)
SELECT n.column_a,
       TO_NUMBER (n.column_a,
                  '9999999999D9999999999999999999999999999',
                  'nls_numeric_characters= ''.,''') as column_a_as_number
FROM my_numbers n;

In this example:

• We create a CTE named my_numbers that contains the same values as in Solution 1.
• The outer query selects from the CTE and applies the same formatting and conversion pattern.

Best Practices

When dealing with ORA-01722 errors, here are some best practices to keep in mind:

• Verify your data: Double-check your data to ensure that it is accurate and consistent. Leading zeros, commas, or other non-numeric characters can cause issues.
• Use the correct formatting: Use the correct format mask for converting VARCHAR2 columns to NUMBER. Make sure to exclude any non-numeric characters from being considered valid digits.
• Test your queries: Test your queries thoroughly before running them against large datasets to ensure that they work as expected.

By following these best practices and using one of the solutions outlined in this article, you can resolve ORA-01722 errors and convert VARCHAR2 columns to NUMBER successfully.

Last modified on 2023-07-10