Escape special characters

Certain characters such as “_” and “%” are not interpreted literally because they have special meaning within databases.

Few databases like Oracle and DB2 allows the assignment of special escape characters to tell database that the character is interpreted literally.

For example consider the below table:
EMPLOYEE
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore
2             Pravin             Mumbai
3             Kiran              Pune
4             Rahul              Chennai

From the above table if we would like to fetch all employees whose contains “_” then the query and result is:
select * from EMP_TEMP where name like '%_%'
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore
2             Pravin             Mumbai
3             Kiran              Pune
4             Rahul              Chennai

So we didn’t get what we expected instead we got all records. As a solution to this Oracle or DB2 supports an ESCAPE clause to tell data base that the character is interpreted literally.

The modified query is
select * from EMP_TEMP where name like '%¼_%' ESCAPE ‘¼’
The character symbol ¼ is used because it can rarely occur in search criteria.

The result of the modified query is
--------------------------------------------
ID           NAME           LOCATION
--------------------------------------------
1             Raj_esh          Bangalore