Can't ESCAPE from SQL?

ESCAPE from SQL with this detailed tip.

Suppose you have following table called Employee.

 
ENAME       ENUM    DEPTCD      DEPTLOC

King        7839      XX        NATWEST      
Blake       7698      YY        LONDON 
Gary        4788      FF        MELBOURNE 
Henry       7644      DD        MOSCOW  
Clark       7782      ZZ        SWIS%ZUIRCH 
Jack        7423      CC        MONTREAL
Jones       7566      AA        NEW_DELHI 

Most of you must have used LIKE operator in SQL. SQL uses the two characters % and _ with the LIKE operator which enables you to perform a Wildcard search.

Here % represents any sequence of zero or more characters and _ represents any single character.

If you want to see only those records in which the column DEPTLOC starts from 'M', the query will be:

SELECT * FROM EMPLOYEE WHERE DEPTLOC LIKE('M%')

The outcome of this query will be the rows having MELBOURNE, MOSCOW, MONTREAL in DEPTLOC column.

If you want to see only those records in which the second character in column DEPTLOC is 'O' then the query will be:

SELECT * FROM EMPLOYEE WHERE DEPTLOC LIKE('_O%') The outcome of this query will be the rows having LONDON, MOSCOW, MONTREAL in DEPTLOC column.

In a similar way, you can use the combination of % and _ to perform a Wildcard search which is always of great use.

But the catch lies here! What will you do if these two special characters % and _ are part of the column data at which you want to use LIKE operator?

Suppose, for some reason, you want to keep the two characters % and _ as a part of your data in any particular column (DEPTLOC in present case). Have a look at the data in column DEPTLOC in 5th and 7th rows where the column DEPTLOC has values SWIS%ZUIRCH and NEW_DELHI respectively.

Now, if you need to see ONLY that row which is having NEW_DELHI in its DEPTLOC column, what will be the query?? If your answer is:

SELECT *FROM EMPLOYEE WHERE DEPTLOC LIKE('N___%')

Then I must tell you that it will show you two rows in DEPTLOC column starts from 'N' i.e. NATWEST and NEW_DELHI. But you wanted to see ONLY that row which is having NEW_DELHI in its DEPTLOC column and not both the rows.

In such a situation, you can use ESCAPE identifier to search for '%' or '_' in the column data. The ESCAPE identifier prevents SQL from interpreting % and _ as a wildcard and thus instructs SQL to interpret % and _ literally. Therefore whenever you need to have an exact match for actual '%' and '_' characters, use the ESCAPE identifier. The ESCAPE identifier specifies what the ESCAPE character is. So, If you need to see ONLY that row which is having NEW_DELHI in its DEPTLOC column, the query will be:

 SELECT *FROM EMPLOYEE WHERE DEPTLOC LIKE('%_%') ESCAPE '/'   

This will show you ONLY that row which is having NEW_DELHI in its DEPTLOC column. I have used character backslash which precedes the underscore _ inside the argument for LIKE operator and have defined it as an ESCAPE character in above query. The ESCAPE character tells SQL that the character next to it in argument for LIKE (which is _ in present case) is to be interpreted literally and not as a wildcard. Thus by using ESCAPE identifier, you can view ONLY those rows which might be having % and _ as part of their column data.

And the good news is that there is no restriction on usage of a character as an ESCAPE character. You can use almost all the characters on your keyboard as ESCAPE characters. So instead of the query written above, even if you wrote:

SELECT *FROM EMPLOYEE WHERE DEPTLOC LIKE('%*_%') ESCAPE '*'

or if you wrote:

SELECT *FROM EMPLOYEE WHERE DEPTLOC LIKE('%9_%') ESCAPE '9'

These queries will give you the same results. But please refrain from using % and – as ESCAPE characters for obvious reasons.

Much discussion on ESCAPE identifier. Here is one simple question at last?Can you now write query by which you can see ONLY that row which is having SWIS%ZUIRCH in its DEPTLOC column? I hope you can, it is:

SELECT *FROM EMPLOYEE WHERE DEPTLOC LIKE('%$%%') ESCAPE('$')

Good Luck with the ESCAPE characters!

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.


This was first published in December 2002

Dig deeper on iSeries CL programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close