SQL LIKE is an outstanding function for wildcard searches. Unfortunately you are faced with some challenges if the LIKE expression does not contain any wildcard characters and the column you are comparing with is a column of type CHAR. Luckily for us an easy solution exists.

Imagine table PERSON contains a column named FIRSTNAME and defined as CHAR(20). In your program you have inserted a SQL statement used for finding first names based on a wildcard:

SELECT FIRSTNAME, MIDDLENAME, LASTNAME
FROM PERSON
WHERE FIRSTNAME LIKE :firstNameFilter

First trick is to define the host variable firstNameFilter corresponding to a VARCHAR(20) column definition. When you assign a value to this host variable please remove all trailing blanks. If the last character i firstNameFilter is a %-character the above SQL statement will work as expected. Unfortunately it will not work as expected in all other cases unless the first name is 20 characters long. Not many first names are that long. The challenge is the trailing blanks in the FIRSTNAME column. Fortunately the cure is simple:

SELECT FIRSTNAME, MIDDLENAME, LASTNAME
FROM PERSON
WHERE STRIP(FIRSTNAME,T) LIKE :firstNameFilter

Now the wildcard search will work correctly no matter what value firstNameFilter is assigned. Moreover DB2 version 8 and later is able to use an index on FIRSTNAME even though it is surrounded by a function.