Using the ESCAPE
Character Option
If the search string contains either of the wildcard symbols, the ESCAPE
clause can be used to specify an escape character.The escape character must precede the ‘%
’ or ‘_
’ symbol in the search string, to indicate that the symbol is to be interpreted as a literal character.
Examples using LIKE
-
Find the numbers of departments whose names start with the word “Software”:
SELECT DEPT_NO FROM DEPT WHERE DEPT_NAME LIKE 'Software%';
It is possible to use an index on the DEPT_NAME field if it exists.
NoteAboutLIKE
and the OptimizerThe
LIKE
predicate itself does not use an index.However, if the predicate takes the form ofLIKE 'string%'
, it will be converted to theSTARTING WITH
predicate, which will use an index.This optimization only works for literal patterns, not for parameters.So, if you need to search for the beginning of a string, it is recommended to use the [fblangref50-commons-predstartwith] predicate instead of the
LIKE
predicate. -
Search for employees whose names consist of 5 letters, start with the letters “Sm” and end with “th”. The predicate will be true for such names as “Smith” and “Smyth”.
SELECT first_name FROM employee WHERE first_name LIKE 'Sm_th'
-
Search for all clients whose address contains the string “Rostov”:
SELECT * FROM CUSTOMER WHERE ADDRESS LIKE '%Rostov%'
NoteIf you need to do a case-insensitive search for something enclosed inside a string (
LIKE '%Abc%'
), use of the [fblangref50-commons-predcontaining] predicate is recommended, in preference to theLIKE
predicate. -
Search for tables containing the underscore character in their names.The ‘
#
’ character is used as the escape character:SELECT RDB$RELATION_NAME FROM RDB$RELATIONS WHERE RDB$RELATION_NAME LIKE '%#_%' ESCAPE '#'