Monday, June 18, 2007

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

1 comment:

  1. Anonymous11:05 AM

    If you want to escape the ampersand character '&', the above will not work.

    Here is a possible solution from ora faqs:

    SET DEFINE OFF
    SELECT 'You & me' FROM DUAL;

    ReplyDelete