Blog
DB2utor

Categories

April 05, 2011

ESCAPE to Find That Special Character

Have you ever found yourself trying to search a string by finding characters like percent (%) or underscore (_)? I'm occasionally asked about this, so I figured it would be a good topic for discussion.

When you're searching for some pattern within a string, you need to use the LIKE predicate. The format of the LIKE predicate is:

match-expression LIKE pattern-expression  ESCAPE escape-expression

"match-expression" is the string to be tested for the given pattern. "pattern-expression" contains the string you're searching for.

The underscore and percent characters in the pattern have special meanings (unless "escape-expression" is specified). The percent sign returns any number of characters; the underscore returns one character.

So, to find all names that start with "TR," I'd use "TR%" as the pattern-expression. To find all four character names that start with TR, I'd use "TR__" (TR followed by two underscores).

The escape-expression is needed when you're looking for a match-expression that contains either the percent or underscore character. The idea is to pick a character to be used to tell DB2 that the next character is a special character (% or _), and to not treat that next character as a pattern used for wild card matching.

I was asked how to find a string that ends in percent sign. This is how you would find all first names that end with "%". I’m using the plus sign (+) as the escape character. You should pick a character that wouldn't normally be contained in the string.

For example:

FIRST_NAME             LAST_NAME
SARAH%                    WHITE   
AN_DREW                 GARCIA  
CHRISTOPHER           BAKER   
MEGAN                     LEWIS   
STEPHANIE               HILL    
JACOB                      HILL    
KEVIN                       HERNANDEZ
BRIANNA                  LEE     
AMANDA                  ALLEN   
HANNAH                  ROWN   


SELECT FIRST_NAME, LAST_NAME                     
FROM PROD.EMPLOYEE                    
WHERE EMPID BETWEEN 1 and 1000                   
  AND STRIP(FIRST_NAME) LIKE '%+%' ESCAPE '+'

With the preceeding data, this query would return: SARAH%    WHITE

SELECT FIRST_NAME, LAST_NAME                     
FROM PROD.EMPLOYEE                    
WHERE EMPID BETWEEN 1 and 1000                   
  AND STRIP(FIRST_NAME) LIKE 'AN+_%' ESCAPE '+'

With this preceeding data, the query would return: AN_DREW                 GARCIA