LIKE operator

Description

An expression with the LIKE operator returns TRUE if the text string matches the given pattern.

If the pattern does not contain percent signs or underscores, it is interpreted literally, in which case LIKE acts as an equality operator:

SELECT
    'Tengri' LIKE 'Tengri' AS result;
+--------+
| result |
+--------+
| true   |
+--------+

If a pattern has special characters, it is interpreted as a regular expression rather than literally:

  • An underscore _ in the pattern corresponds to any single character.

  • The percent sign % in the pattern corresponds to any sequence of zero or more characters.

The LIKE pattern match is always applied to the entire string. Therefore, if you want to match a sequence anywhere in the string (cover a substring with the pattern), the pattern must begin and end with the % percent sign.

You can also use the opposite expressions <string> NOT LIKE <pattern> and NOT string LIKE <pattern>:

SELECT
    NOT 'Tengri' LIKE 'Tengri' AS result_1,
    'Tengri' NOT LIKE 'Tengri' AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| false    | false    |
+----------+----------+

ILIKE operator

The ILIKE operator can be used instead of LIKE to make matching case insensitive:

SELECT
    'Tengri' ILIKE 'tengri' AS result_1,
    'Tengri' ILIKE '%NGRi'  AS result_2;
+----------+----------+
| result_1 | result_2 |
+----------+----------+
| true     | true     |
+----------+----------+

Examples

  • Let’s show some examples of how LIKE templates work:

    SELECT
    'Tengri' LIKE 'TNGRi'  AS result_1,
    'Tengri' LIKE 'T%'     AS result_2,
    'TNGRi'  LIKE 'T%'     AS result_3,
    'Tengri' LIKE 'T_____' AS result_4;
    +----------+----------+----------+----------+
| result_1 | result_2 | result_3 | result_4 |
+----------+----------+----------+----------+
| false    | true     | true     | true     |
+----------+----------+----------+----------+

  • Let us select from the table of capitals the countries in which the capital begins with M:

    CREATE TABLE capitals (country VARCHAR, capital VARCHAR);
INSERT INTO capitals VALUES
    ('Russia', 'Moscow'),
    ('Italy', 'Rome'),
    ('Spain', 'Madrid'),
    ('France', 'Paris');

SELECT country FROM capitals WHERE capital LIKE 'M%';
    +---------+
| country |
+---------+
| Russia  |
+---------+
| Spain   |
+---------+

See also