Escape From SQL
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.
Author
Stewart Schatz
Average Rating
2 thoughts on “Escape From SQL”
Leave a Reply Cancel reply
This site uses Akismet to reduce spam. Learn how your comment data is processed.
More Stories
Quick Tip: Hold All JDE Job Queues
The following SQL statement will put all JDE Job Queues on hold: UPDATE SY920.F986130 SET QCQUSTS='02' This statement is very...
How To Disable Client Package Deployment
If you have a development client that has been neglected and JDE wants you to install 15 update packages but...
Quick Tip: Limit The Number Of Rows Returned From A SQL Query
This is pretty basic, but I had to look up the syntax to use for SQL on IBM DB2 so...
Quick Tip: Where Is An Application On The Menu?
The following SQL will give you a good idea if an application or UBE is on the menu and where...
Quick Tip: Decline All Update Packages Using SQL
A quick way to decline all packages that have been marked for deployment on a development client is use SQL:...
Quick Tip: Change A Scheduled Job’s Time Zone Using SQL
If you work with multiple time zones, especially if one uses Daylight Saving Time and the other does not, it...
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
Tim,
That’s totally possible. I don’t have any experience with Oracle databases.
Thanks for the comment!