Pattern Matching

• MySQL provides standard SQL pattern matching.
  • It provides pattern matching based on extended regular expressions.
    • Use REGEXP and NOT REGEXP operators.
    • Use or RLIKE and NOT RLIKE, which are synonyms.
  • It also provides another pattern matching scheme similar to extended regular expressions.
    • Use _ to match any single character.
    • Use % to match zero or more characters.
    • Do not use =or <> withSQL patterns.
      • Use LIKE or NOT LIKE comparison operators instead.

• Here are some examples using LIKE and its REGEXP equivalent:
  • To find names beginning with ‘a’:

    mysql> SELECT * FROM pet WHERE name LIKE 'a%';

    mysql> SELECT * FROM pet WHERE name REGEXP '^a';

  • To find names ending with ‘nd’:

    mysql> SELECT * FROM pet WHERE name LIKE '%nd';

    mysql> SELECT * FROM pet WHERE name REGEXP 'nd$';

  • To find names containing ‘k’:

    mysql> SELECT * FROM pet WHERE name LIKE '%k%';

    mysql> SELECT * FROM pet WHERE name REGEXP 'k';

  • To find names containing exactly two characters:

    mysql> SELECT * FROM pet WHERE name LIKE '__';

    mysql> SELECT * FROM pet WHERE name REGEXP '^..$';

Are there other operations?

---

---

© 2007 John Michael Pierobon

Notes