DB2 TIPS : LIKE predicate in DB2

The LIKE predicate offers a great deal of flexibility and power to your SQL statements. Using LIKE you can quickly retrieve data based on patterns and wildcards. However, some uses of LIKE can be confusing to implement appropriately—especially when LIKE is used with host variables. Let's assume that you need to create an application that retrieves employees by last name, but the supplied value for the last name can be either the entire name or just the first few bytes of that name. In that case, the following query can suffice: SELECT EMPNO, FIRSTNME, LASTNAME FROM   DSN8810.EMP WHERE  LASTNAME LIKE :host_variable; In order for this to work, when you enter the value for host_variable, the host_variable always append percent signs (%) to the end of the value. The percent sign specifies that DB2 should accept as a match any number of characters (including 0). This must be done programmatically. So, if the value entered is SM, the host_variable should contain SM%%%%%%%% and if the value entered is SMITH, the host_variable should contain SMITH%%%%%. Append as many percent signs as required to fill up the entire length of the host variable. Failure to do this will result in DB2 searching for blank spaces. Think about it—if you assign SMITH% to a 10-byte host variable, that host variable will think it should search for SMITH%, that is SMITH at the beginning, four blanks at the end, and anything in the middle. So, for SMITH%%%%%, SMITH will be returned, and so SMITHLY (or any name beginning with SMITH). There is no way to magically determine if what was entered is a complete name or just a portion thereof. If this is not acceptable, then a single query will not likely to be feasible. Instead, you would have to ask the user to enter whether a full name or just a portion is being entered.