ORA-01722: invalid number

On executing the to_number function, converting character or date values to a number can cause an ORA error if they do not match a numeric format. If the table contains any special characters or letters, Oracle will throw an ORA error when attempting to convert to a number.

My id column is of varchar type and contains numeric data. It was working fine until yesterday, but this morning I encountered the error ORA-01722. Checking the data inserted last night, there may be a special character or letter that was mistakenly added during the ETL process.

SQL> select to_number(id) from test;

ERROR: ORA-01722: invalid number

I found that this error occurs when a string cannot be converted to a number because it’s not a valid number. Only numeric fields or character fields containing numbers can be used in arithmetic functions, and only numeric fields can be added to or subtracted from dates.

Action:
Verify that the character strings in the function or expression include only numbers, a sign, a decimal point, and “E” or “e”, then try again.

Solution:
If my table has special characters instead of numeric values, we need to check the data and find the rows that contain those special characters to fix the issue.

For find we use regular expression query to find non numeric data in table

Syntax:
SQL> select column_name from table_name where REGEXP_LIKE (column name, '[^0123456789.]');

Example:
SQL> select id from test where REGEXP_LIKE (id, '[^0123456789.]');

ID
------------------------------------------------------------------
a
3434,3333.000
abc

Note: correct this lines will fixed the above error as data.