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.

No comments :

Post a Comment