ESCAPE with LIKE

The ESCAPE clause can specify an escape character that is different from the default escape character. The default escape character is set by the DEFAULTESCCHAR configuration parameter or the DEFAULTESCCHAR session environment option.

For example, if you specify z in the ESCAPE clause, then a quoted string operand that included z_ is interpreted as including a literal underscore ( _ ) character, rather than _ as a wildcard. Similarly, z% is interpreted as a literal percent ( % ) sign, rather than % as a wildcard. Finally, the characters zz in a string would be interpreted as single literal z. The following statement retrieves rows from the customer table in which the company column includes a literal underscore character:

SELECT * FROM customer WHERE company LIKE '%z_%' ESCAPE 'z';

You can also use a host variable that contains a single character. The next statement uses a host variable to specify an escape character:

EXEC SQL BEGIN DECLARE SECTION;
   char escp='z';
   char fname[20];
EXEC SQL END DECLARE SECTION;
EXEC SQL select fname from customer 
   into :fname where company like '%z_%' escape :escp;