Escaping a Character in Oracle

Consider the following table

select * from my_table

NAME
----
FAHD
FAHD_SHARIFF
SHARIFF_FAHD
FAHDSHARIFF

If you want to select only those names containing an underscore, the following query will NOT work:


select * from my_table where name like '%_%'

NAME
----
FAHD
FAHD_SHARIFF
SHARIFF_FAHD
FAHDSHARIFF

All rows are returned even though rows 1 and 4 do not contain an underscore! This is because an underscore is a special character - it is a single character wildcard.

You need to escape the underscore so that Oracle treats it as a literal:


select * from my_table where name like '%\_%' escape '\'

NAME
----
FAHD_SHARIFF
SHARIFF_FAHD