ORA-01722: invalid number exception while using "escape"

Question

I have query with special characters and I am using "escape" in it. It works fine for single filter but multiple filters it gives ORA-01722: invalid number exception. Example:

    <some query> 
    and this_.NAME like  '%<some_string>%' escape '\'   
    and this_.ID IN ()

this will throw exception. But, If I change order, like below, it works:

    <some query> 
    and this_.ID IN ()
    and this_.NAME like  '%<some_string>%' escape '\'

any help would be appreciated.

Are you sure that both queries returned all rows? Some GUI tools return first 100 or 500 rows, but not the whole set. If you navigate to the end (or, simply, use SELECT DISTINCT), you might notice that the second query (with the "changed order") fails as well. — Littlefoot
Hm, that's strange. Just to make sure: try to remove (comment) the and this_.NAME like ... and run the query; what happens? Then comment the and this_.ID ... and un-comment the and this_.NAME ... and run it again. Why? To find out which one of those conditions is responsible for the error & narrow further investigation. — Littlefoot
If I put single condition like either this_.NAME or this_.ID it works. — Akash Shinde
I don't see what escape has to do with it. Is there even a '\' in your wildcard? — William Robertson

Lalit Kumar B Lalit Kumar B · Accepted Answer · 2018-03-06T07:38:11

It works fine for single filter but multiple filters it gives ORA-01722: invalid number exception.

It should work for multiple filter predicates.

For example,

SQL> WITH DATA AS(
  2  SELECT 1 cola, 'a%b%c' colb FROM dual
  3  UNION ALL
  4  SELECT 2 cola, 'abc' colb FROM dual
  5  )
  6  SELECT * FROM DATA
  7  WHERE colb LIKE '%\%b\%%' ESCAPE '\'
  8  AND cola IN (1,2);

      COLA COLB
---------- -----
         1 a%b%c

ORA-01722: invalid number exception while using "escape"

1 Answers