Escape From SQL

Read Time:47 Sec

The other day, one of my coworkers was presented a question from our development team:

How can I use SQL to look for a "%" in a column?

Well, the first guess is to use something like:

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%%%'

or

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%%%'

Well, unfortunately, neither of those statements would work. However, DB2/OS400 provides a very cool way of escaping a special character. It is the ESCAPE keyword. This keyword allows you to specify the character that you would like to use as an escape character. So, this would be the correct syntax:

SELECT * FROM MYLIB/MYFILE WHERE MYCOL LIKE '%+%%' ESCAPE '+'

I think that this is a very cool feature. I just wish that the ANSI/ISO standards used it. Then, it could be used in other databases.

Technorati Tags: sql,ibm,as400,system i,wildcard,escape,db2

Author

Stewart Schatz

Career: Principal CNC Consultant for Syntax Systems Limited specializing Oracle JD Edwards EnterpriseOne and the technology that supports it. Side Hustle: Owner/Operator of E1Tips.com Location: Lancaster, PA USA  What I like to do: Invest in Family, Explore Technology, Lead Teams, Share Knowledge/Experience, Hunt, Hike, etc.

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

2 thoughts on “Escape From SQL

  1. I thougt it is ANSI/ISO standard. It works in oracle 10g. It is possibly a new standard that doesn’t work in 9i or older.

    TS

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post EnterpriseOne Ad Hoc Reporting
Next post Numeric Will Never Equal Alpha