LIKE Predicate

Description

A LIKE predicate is used to search for a specific pattern. This predicate also supports multiple patterns with quantifiers include ANY, SOME and ALL.

Syntax

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

Parameters

  • search_pattern

    Specifies 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

    Specifies the escape character. The default escape character is \.

  • regex_pattern

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

  • quantifiers

    Specifies the predicate quantifiers include ANY, SOME and ALL. ANY or SOME means if one of the patterns matches the input, then return true; ALL means if all the patterns matches the input, then return true.

Examples

  1. CREATE TABLE person (id INT, name STRING, age INT);
  2. INSERT INTO person VALUES
  3.     (100, 'John', 30),
  4.     (200, 'Mary', NULL),
  5.     (300, 'Mike', 80),
  6.     (400, 'Dan',  50),
  7.     (500, 'Evan_w', 16);
  9. SELECT * FROM person WHERE name LIKE 'M%';
  10. +---+----+----+
  11. | id|name| age|
  12. +---+----+----+
  13. |300|Mike|  80|
  14. |200|Mary|null|
  15. +---+----+----+
  17. SELECT * FROM person WHERE name LIKE 'M_ry';
  18. +---+----+----+
  19. | id|name| age|
  20. +---+----+----+
  21. |200|Mary|null|
  22. +---+----+----+
  24. SELECT * FROM person WHERE name NOT LIKE 'M_ry';
  25. +---+------+---+
  26. | id|  name|age|
  27. +---+------+---+
  28. |500|Evan_W| 16|
  29. |300|  Mike| 80|
  30. |100|  John| 30|
  31. |400|   Dan| 50|
  32. +---+------+---+
  34. SELECT * FROM person WHERE name RLIKE 'M+';
  35. +---+----+----+
  36. | id|name| age|
  37. +---+----+----+
  38. |300|Mike|  80|
  39. |200|Mary|null|
  40. +---+----+----+
  42. SELECT * FROM person WHERE name REGEXP 'M+';
  43. +---+----+----+
  44. | id|name| age|
  45. +---+----+----+
  46. |300|Mike|  80|
  47. |200|Mary|null|
  48. +---+----+----+
  50. SELECT * FROM person WHERE name LIKE '%\_%';
  51. +---+------+---+
  52. | id|  name|age|
  53. +---+------+---+
  54. |500|Evan_W| 16|
  55. +---+------+---+
  57. SELECT * FROM person WHERE name LIKE '%$_%' ESCAPE '$';
  58. +---+------+---+
  59. | id|  name|age|
  60. +---+------+---+
  61. |500|Evan_W| 16|
  62. +---+------+---+
  64. SELECT * FROM person WHERE name LIKE ALL ('%an%', '%an');
  65. +---+----+----+
  66. | id|name| age|
  67. +---+----+----+
  68. |400| Dan|  50|
  69. +---+----+----+
  71. SELECT * FROM person WHERE name LIKE ANY ('%an%', '%an');
  72. +---+------+---+
  73. | id|  name|age|
  74. +---+------+---+
  75. |400|   Dan| 50|
  76. |500|Evan_W| 16|
  77. +---+------+---+
  79. SELECT * FROM person WHERE name LIKE SOME ('%an%', '%an');
  80. +---+------+---+
  81. | id|  name|age|
  82. +---+------+---+
  83. |400|   Dan| 50|
  84. |500|Evan_W| 16|
  85. +---+------+---+
  87. SELECT * FROM person WHERE name NOT LIKE ALL ('%an%', '%an');
  88. +---+----+----+
  89. | id|name| age|
  90. +---+----+----+
  91. |100|John|  30|
  92. |200|Mary|null|
  93. |300|Mike|  80|
  94. +---+----+----+
  96. SELECT * FROM person WHERE name NOT LIKE ANY ('%an%', '%an');
  97. +---+------+----+
  98. | id|  name| age|
  99. +---+------+----+
  100. |100|  John|  30|
  101. |200|  Mary|null|
  102. |300|  Mike|  80|
  103. |500|Evan_W|  16|
  104. +---+------+----+
  106. SELECT * FROM person WHERE name NOT LIKE SOME ('%an%', '%an');
  107. +---+------+----+
  108. | id|  name| age|
  109. +---+------+----+
  110. |100|  John|  30|
  111. |200|  Mary|null|
  112. |300|  Mike|  80|
  113. |500|Evan_W|  16|
  114. +---+------+----+