Searching for Wildcard Characters

Published: May 23, 2007

by Ted Holt

If you happen to see a bald-headed old geezer staring at a shelf at the local home-improvement warehouse (motto: you can do it; we can laugh), that's me. I don't know what I'm looking for, but I'm foolish enough to think I'll recognize it when I see it. My methodology is something like searching a database using wildcard characters, which is what today's tip is about.

You probably know that database query interfaces typically allow for two types of wildcards: a wildcard that matches exactly one character; and a wildcard that matches zero or more characters. In SQL, my database query interface of choice, these characters are the underscore and percent sign, respectively. For example, to find all rows (records) of a table (file) that have a capital C anywhere in column (field) DataField, use this SQL command:

SELECT * FROM qtemp.SomeTable
 WHERE datafield LIKE '%C%';   

But suppose you want to search for rows that have a percent sign anywhere in DataField. You can't do this:

SELECT * FROM qtemp.SomeTable
 WHERE datafield LIKE '%%%';   

Instead, add an ESCAPE character to the query. In the following example, the backslash is defined as the escape character.

SELECT * FROM SomeTable
 WHERE DataField LIKE '%10\%%' ESCAPE '\'

The escape character tells SQL that the character that follows it is to be interpreted literally. This query searches for all rows containing the string 10%. The first and last percent signs are wildcards. The second percent sign is interpreted literally.

I realize that some shops don't have SQL, so here's an OPNQRYF example. The wildcard characters for OPNQRYF are underscore (one character) and asterisk (zero or more characters.) You can change them to whatever you like by specifying the wildcard characters in the optional, second parameter of the %WLDCRD function. The following query retrieves records that contain B followed by an underscore.

OPNQRYF FILE((SOMETABLE)) +
   QRYSLT('DataField = %wldcrd("*B_*" "&*") ') 

The second parameter of wildcard means that the ampersand is to be used as the wildcard for one character, while the asterisk serves as the wildcard for zero or more characters.

I also realize that many shops use Query for iSeries, or whatever it's called these days. To the best of my knowledge, there is no way to override the wildcard characters. That sounds like yet another reason to dump Query for a better query tool.

SQL rulz!

--Ted

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot