FirebirdSQL logo
 Data Types and SubtypesData Definition (DDL) Statements 
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

  1. 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.

  2. 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'

  3. 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.

  4. 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 '#'
See also

STARTING WITH, CONTAINING, SIMILAR TO

STARTING WITH
Syntax
<value> [NOT] STARTING WITH <value>

The STARTING WITH predicate searches for a string or a string-like type that starts with the characters in its value argument.The case- and accent-sensitivity of STARTING WITH depends on the collation of the first value.

When STARTING WITH is used in the search conditions of DML queries, the Firebird optimizer can use an index on the searched column, if it exists.

Example

Search for employees whose last names start with “Jo”:

SELECT LAST_NAME, FIRST_NAME
FROM EMPLOYEE
WHERE LAST_NAME STARTING WITH 'Jo'
See also

LIKE