CODEWITHSUNDEEP
sqloracle-databaseora-01722
Harry
Harry

Reputation: 1353

Oracle LIKE returns (ORA01722)

I have got an ID field with IDs (varchar2) like U123JAS and P239BB.

During my query I perform a like filter:

ID LIKE ('U20Q%') //works perfectly.

but: when i do:

ID LIKE ('U20%') //it fails

ErrorCode

[Error Code: 1722, SQL State: 42000]  ORA-01722: invalid number

Why?

SOLUTION:

The Error was caused by (tadaa) an invalid number cast in a totally different column. The DataRow which was causing the error only hit the second like.

Thank you all for your help, may my failure help someone else in future :D

Upvotes: 3

Views: 3719

Answers (1)

Benoit
Benoit

Reputation: 79205

I think that there is a number conversion somewhere else in your query. It's simply not evaluated in the first case, because the row that triggers the error hasn't id beginning with U20Q, but has id beginning with U20.

When it's necessary to convert the number, in the second case, you have a row for which it fails.

Try this:

SELECT * FROM dual WHERE 1 < dummy AND 1 = 0; /* works */

SELECT * FROM dual WHERE 1 < dummy AND 1 = 1; /* ORA-01722 */

Upvotes: 3

Related Questions