How to Resolve an Invalid Number Error When Using Oracle TO_NUMBER

Are you attempting to use a TO_NUMBER function, but getting an invalid number error? Learn how to resolve it in this article.

What Is the Invalid Number Error?

The invalid number error happens when Oracle attempts to convert a string to a number field but can't.

This is often because the supplied string value is not a number (e.g. it is a letter or punctuation character).

You'll get an error like this in your output:

ORA-01722: invalid number

So, how can you resolve this if you're already trying to convert a value into a number?

First, Check The Data You Have Entered

Sometimes, checking the data you have entered can be the best way to solve this problem. It could be that you have not entered the value correctly inside the TO_NUMBER function.

So, check that your function input:

• Does not contain any letters

• Does not contain any punctuation symbols or extra characters

• Does not contain any spaces or tab characters

Check the Parameter Separately

If you like, you can SELECT the parameter you're using as a separate column, to check what the value is.

For example, if you're trying to run this statement:

 SELECT TO_NUMBER(age) FROM student; 

You can then see which values are stored in the age column, and see which values might be causing issues.

You can use DISTINCT to get a unique list of values as well:

 SELECT DISTINCT age FROM student; 

This might help you identify if there are any outstanding values.

Check the WHERE Clause for Implicit Conversion

The final suggestion I have for resolving the invalid number error when using TO_NUMBER is to check the WHERE clause for any implicit conversions that might be performed.

For example, your statement might be:

SELECT s.first_name, s.last_name
FROM student
INNER JOIN class c ON s.class_id = c.class_id
WHERE TO_NUMBER(fees_paid) > c.minimum_fees;

If Oracle has found that the class.minimum_fees column is not a number value (such as a VARCHAR2), then the query may fail because it is trying to compare a number to a string.

Or it could be the other way around - the fees_paid column is not a string.

In this situation, confirm the definition of these tables, and try your statements again. You might be able to alter your table, but if you, you'll need to alter your SELECT statement.

Hopefully, this article has helped to clarify the reasons you might be getting an invalid number error when using the TO_NUMBER function.