LIKE 谓词

  • 项目

搜索特定模式。 此谓词还支持多个模式，限定符包括 ANY、SOME 和 ALL。

语法

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

参数

  • search_pattern

    要通过 LIKE 子句搜索的字符串模式。 它可包含特殊的模式匹配字符：

    • % 与零个或多个字符匹配。
    • _ 与一个字符完全匹配。

  • esc_char

    转义字符。 默认转义字符为 \。

  • regex_pattern

    要通过 RLIKE 或 REGEXP 子句搜索的正则表达式搜索模式。

  • 限定符

    谓词限定符包括 ANY、SOME 和 ALL。 如果其中一个模式与输入匹配，ANY 或 SOME 返回 true；如果所有模式与输入匹配，ALL 返回 true。

示例

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| +—+——+—-+