How to escape underscores in Oracle SQL Developer?

Question

If I want to select strings with underscores using Oracle SQL Developer, how do I have to escape those?

I tried:

name like '%_%' 
name like '%'_%' 
name like '%\_%' 

but none of this helped.

Answer 1

You need to use the explicit escape; in this way you can decide a character to use for escaping and then use it in your LIKE.

For example, here I use the '!' to escape special characters:

select str
from (
        select 'a_b' str from dual union all
        select 'ab'      from dual
     )
where str like '%!_%' escape '!' 

gives

STR
---
a_b

Answer 2

Oracle suggests the code below which workded for me:

SELECT last_name 
    FROM employees
    WHERE last_name LIKE '%A\_B%' ESCAPE '\'
    ORDER BY last_name;