SQL Select Error: “ORA-01722: invalid number” only when number is queried

Other questions about this error message seem to be in situations where an insert is being performed or text is being converted to a number. In my case, when I select by a specific number from my_table I get the error “ORA-01722: invalid number” and when I use a different number I don’t.

This code triggers the error message:

SELECT * FROM my_table WHERE sel_num = 156396255

This code returns the expected results (1 row from my_table):

SELECT * FROM my_table WHERE sel_num = 156396320

The sel_num field is defined in my_table as:

Column Name: sel_num, Data Type: VARCHAR2 (30 byte), Null?: Y

I have no control over the data type unfortunately.

Oracle states that this error is caused by:

“The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.”

This is confusing considering I am passing a number into the WHERE clause, not a string. Due to the VARCHAR2 data type, I tried passing the number as a string into the query and this caused the error message to go away.

This code returns an empty set with no error message:

SELECT * FROM my_table WHERE sel_num = '156396255'

This may an acceptable result if this specific value for sel_num does not exist in the table, but I am unsure why this seems to be working the way it does.

Answer

There is nothing confusing about the version where you provide a number value in the WHERE clause. Because your value is a number Oracle attempts to convert the varchar2 column values into a number data type for comparison. Some of the columns do not contain valid numeric data so an error occurs. If you enclose the value you want to search for in single quotes now you are doing a character to charter compare. Problem if the table data column stores any non-digits in the value which then match your value not match. So you may have to filter for leading and trailing spaces, remove dollar signs or commas etc… This is an example of 1) why the appropriate database data type should always be used to store data and 2) why data should always be normalized (edited): case, leading and/or trailing spaces, zero filled, etc….