Using LIKE

Go Up to Using Comparison Operators in Expressions

LIKE is a case-sensitive operator that tests a string value against a string containing wildcards, symbols that substitute for a single, variable character, or a string of variable characters. LIKE recognizes two wildcard symbols:

% (percent) substitutes for a string of zero or more characters.
_ (underscore) substitutes for a single character.

The syntax for LIKE is:

<value> [NOT] LIKE <value> [ESCAPE 'symbol']

For example, this cursor retrieves information about any employee whose last names contain the three letter combination “ton” (but not “Ton”):

EXEC SQL
DECLARE TON_EMP CURSOR FOR
SELECT LAST_NAME, FIRST_NAME, EMP_NO
FROM EMPLOYEE
WHERE LAST_NAME LIKE '%ton%';

To test for a string that contains a percent or underscore character:

Precede the % or _ with another symbol (for example, @), in the quoted comparison string.
Use the ESCAPE clause to identify the symbol (@, in this case) preceding % or _ as a literal symbol. A literal symbol tells InterBase that the next character should be included as it is in the search string.

For example, this cursor retrieves all table names in RDB$RELATIONS that have underscores in their names:

EXEC SQL
DECLARE UNDER_TABLE CURSOR FOR
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME LIKE '%@_%' ESCAPE '@';

Use NOT LIKE to retrieve rows that do not contain strings matching those described. For example, the following cursor retrieves all the table names in RDB$RELATIONS that do not have underscores in their names:

EXEC SQL
DECLARE NOT_UNDER_TABLE CURSOR FOR
SELECT RDB$RELATION_NAME
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME NOT LIKE '%@_%' ESCAPE '@';

Advance To:

Using IS NULL