LIKE predicate

Searches for a specific pattern. This predicate also supports multiple patterns with quantifiers include ANY, SOME and ALL.

Syntax

[ NOT ] { LIKE search_pattern [ ESCAPE esc_char ] | [ RLIKE | REGEXP ] regex_pattern }
[ NOT ] { LIKE quantifiers ( search_pattern [ , ... ]) }

Parameters

  • search_pattern

    A string pattern to be searched by the LIKE clause. It can contain special pattern-matching characters:

    • % matches zero or more characters.
    • _ matches exactly one character.
  • esc_char

    The escape character. The default escape character is \.

  • regex_pattern

    A regular expression search pattern to be searched by the RLIKE or REGEXP clause.

  • quantifiers

    Predicate quantifiers include ANY, SOME and ALL. ANY or SOME return true if one of the patterns matches the input; ALL returns true if all the patterns matches the input.

Examples

CREATE TABLE person (id INT, name STRING, age INT);
INSERT INTO person VALUES
    (100, 'John', 30),
    (200, 'Mary', NULL),
    (300, 'Mike', 80),
    (400, 'Dan',  50),
    (500, 'Evan_w', 16);

SELECT * FROM person WHERE name LIKE 'M%';
+---+----+----+
| id|name| age|
+---+----+----+
|300|Mike|  80|
|200|Mary|null|
+---+----+----+

SELECT * FROM person WHERE name LIKE 'M_ry';
+---+----+----+
| id|name| age|
+---+----+----+
|200|Mary|null|
+---+----+----+

SELECT * FROM person WHERE name NOT LIKE 'M_ry';
+---+------+---+
| id|  name|age|
+---+------+---+
|500|Evan_W| 16|
|300|  Mike| 80|
|100|  John| 30|
|400|   Dan| 50|
+---+------+---+

SELECT * FROM person WHERE name RLIKE 'M+';
+---+----+----+
| id|name| age|
+---+----+----+
|300|Mike|  80|
|200|Mary|null|
+---+----+----+

SELECT * FROM person WHERE name REGEXP 'M+';
+---+----+----+
| id|name| age|
+---+----+----+
|300|Mike|  80|
|200|Mary|null|
+---+----+----+

SELECT * FROM person WHERE name LIKE '%\_%';
+---+------+---+
| id|  name|age|
+---+------+---+
|500|Evan_W| 16|
+---+------+---+

SELECT * FROM person WHERE name LIKE '%$_%' ESCAPE '$';
+---+------+---+
| id|  name|age|
+---+------+---+
|500|Evan_W| 16|
+---+------+---+

SELECT * FROM person WHERE name LIKE ALL (‘%an%’, ‘%an’); +—+—-+—-+ | id|name| age| +—+—-+—-+ |400| Dan| 50| +—+—-+—-+ SELECT * FROM person WHERE name LIKE ANY (‘%an%’, ‘%an’); +—+——+—+ | id| name|age| +—+——+—+ |400| Dan| 50| |500|Evan_W| 16| +—+——+—+ SELECT * FROM person WHERE name LIKE SOME (‘%an%’, ‘%an’); +—+——+—+ | id| name|age| +—+——+—+ |400| Dan| 50| |500|Evan_W| 16| +—+——+—+ SELECT * FROM person WHERE name NOT LIKE ALL (‘%an%’, ‘%an’); +—+—-+—-+ | id|name| age| +—+—-+—-+ |100|John| 30| |200|Mary|null| |300|Mike| 80| +—+—-+—-+ SELECT * FROM person WHERE name NOT LIKE ANY (‘%an%’, ‘%an’); +—+——+—-+ | id| name| age| +—+——+—-+ |100| John| 30| |200| Mary|null| |300| Mike| 80| |500|Evan_W| 16| +—+——+—-+ SELECT * FROM person WHERE name NOT LIKE SOME (‘%an%’, ‘%an’); +—+——+—-+ | id| name| age| +—+——+—-+ |100| John| 30| |200| Mary|null| |300| Mike| 80| |500|Evan_W| 16| +—+——+—-+