Sky on Tech

It seems like an obvious question, but when someone on an IBM developerWorks forum asked how to use wildcards as literals in SQL I realized I didn't know the answer off hand! How DO you find all the records with a part number that contains an underscore, say? SELECT * FROM partno WHERE part LIKE '%_%' doesn't work as we'd like!

Suppose we have the following data:

   PART
--------------
   test1
   test_1
   test%1
   test2
   test_2
   test%2

and we want to write a query to return JUST part_1 and part_2 because they have an underscore.

If it was any character other than the underscore or percentage sign, we could write:

     SELECT * FROM partno WHERE part LIKE '%_%' 

But this is interpreted as any number of characters, a single character, and any additional number of characters. We need to tell SQL that the underscore should be considered an underscore, not a wildcard.

A quick search on the DB2 LUW documentation turns up the answer, the ESCAPE expression on the LIKE clause where we can define the character we want to use to escape the wildcard. So if we decide to use the slash character in front of the underscore, the following works perfectly:

     SELECT * FROM partno WHERE part LIKE '%\_%' ESCAPE '\'

And just for fun, we can find all the records with a percentage sign in a part number by escaping the middle of three percentage signs with an equal sign:

     SELECT * FROM partno WHERE part LIKE '%=%%' ESCAPE '='