How to escape character in LIKE clause

To escape certain characters such as _ which serve as wild card characters in a SQL Query, there is an extension to the LIKE clause ESCAPE.

The syntax is:

LIKE string ESCAPE character

Why use escape

We will take on an example to make clear its usage. We have a table namely TAB_100 which has only column FIELD_100, which is VARCHAR2(20). The example data in the table is as below.

Code:

FIELD_100 ------------ sample_data sample data not applicable not_applicable

Now if we want to select the rows which have the underscores. What command will you normally issue. It would be naturally the first time like below:

Code:

select FIELD_100 from TABLE_100 where FIELD_100 like '%_%';

But you will be surprised to see the results:

Code:

FIELD_100 ------------ sample_data sample data not applicable not_applicable

Why did this happen? For experienced Oracle campaigners it is well known that _ is a wild card character. That is _ stands for any character. So your query yielded all the rows, which you did not want.

Now we modify the query to get the desired output as below:

Code:

select FIELD_100 from TABLE_100 where FIELD_100 like '%#_%' escape '#';

Now the output is as below:

Code:

FIELD_100 ------------ sample_data not_applicable

This is what you exactly wanted. There is no restriction to only use # character. You can use any character, including _ character.