LIKE 谓词LIKE predicate

搜索特定模式。Searches for a specific pattern.

语法Syntax

[ NOT ] { LIKE search_pattern [ ESCAPE esc_char ] | [ RLIKE | REGEXP ] regex_pattern }

参数Parameters

  • search_patternsearch_pattern

    要通过 LIKE 子句搜索的字符串模式。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_charesc_char

    转义字符。The escape character. 默认转义字符为 \The default escape character is \.

  • regex_patternregex_pattern

    要通过 RLIKEREGEXP 子句搜索的正则表达式搜索模式。A regular expression search pattern to be searched by the RLIKE or REGEXP clause.

示例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|
+---+------+---+