Wildcards

Data Types and SubtypesData Definition (DDL) Statements

Wildcards

Two wildcard symbols are available for use in the search pattern:

the percentage symbol (%) will match any sequence of zero or more characters in the tested value
the underscore character (_) will match any single character in the tested value

If the tested value matches the pattern, taking into account wildcard symbols, the predicate is TRUE.

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.

Note

About LIKE and the Optimizer

The LIKE predicate itself does not use an index.However, if the predicate takes the form of LIKE 'string%', it will be converted to the STARTING 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%'

Note	If 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 the `LIKE` 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 '#'
```

Wildcards

Wildcards

Using the ESCAPE Character Option

Examples using LIKE

Using the `ESCAPE` Character Option

Examples using `LIKE`