MySQL | Regular expressions (Regexp)

MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator.

  1. It provide a powerful and flexible pattern match that can help us implement power search utilities for our database systems.
  2. REGEXP is the operator used when performing regular expression pattern matches. RLIKE is the synonym.
  3. It also supports a number of metacharacters which allow more flexibility and control when performing pattern matching.
  4. The backslash is used as an escape character. It’s only considered in the pattern match if double backslashes have used.
  5. Not case sensitive.
Pattern What the Pattern matches
* Zero or more instances of string preceding it
+ One or more instances of strings preceding it
. Any single character
? Match zero or one instances of the strings preceding it.
^ caret(^) matches Beginning of string
$ End of string
[abc] Any character listed between the square brackets
[^abc] Any character not listed between the square brackets
[A-Z] match any upper case letter.
[a-z] match any lower case letter
[0-9] match any digit from 0 through to 9.
[[:<:]] matches the beginning of words.
[[:>:]] matches the end of words.
[:class:] matches a character class i.e. [:alpha:] to match letters, [:space:] to match white space, [:punct:] is match punctuations and [:upper:] for upper class letters.
p1|p2|p3 Alternation; matches any of the patterns p1, p2, or p3
{n} n instances of preceding element
{m,n} m through n instances of preceding element

Examples with explanation :

  • Match beginning of string(^):
    Gives all the names starting with ‘sa’.Example- sam,samarth.

    SELECT name FROM student_tbl WHERE name REGEXP '^sa';
    

  • Match the end of a string($):
    Gives all the names ending with ‘on’.Example – norton,merton.

    SELECT name FROM student_tbl WHERE name REGEXP 'on$';
    

  • Match zero or one instance of the strings preceding it(?):
    Gives all the titles containing ‘com’.Example – comedy , romantic comedy.

    SELECT title FROM movies_tbl WHERE title REGEXP 'com?'; 
    

  • matches any of the patterns p1, p2, or p3(p1|p2|p3):
    Gives all the names containing ‘be’ or ‘ae’.Example – Abel, Baer.

    SELECT name FROM student_tbl WHERE REGEXP 'be|ae' ;
    

  • Matches any character listed between the square brackets([abc]):
    Gives all the names containing ‘j’ or ‘z’.Example – Lorentz, Rajs.

    SELECT name FROM student_tbl WHERE REGEXP '[jz]' ;
    

  • Matches any lower case letter between ‘a’ to ‘z’- ([a-z]) ([a-z] and (.)):
    Retrieve all names that contain a letter in the range of ‘b’ and ‘g’, followed by any character, followed by the letter ‘a’.Example – Tobias, sewall.

    Matches any single character(.)

    SELECT name FROM student_tbl WHERE REGEXP '[b-g].[a]' ;
    

  • Matches any character not listed between the square brackets.([^abc]):
    Gives all the names not containing ‘j’ or ‘z’. Example – nerton, sewall.

    SELECT name FROM student_tbl WHERE REGEXP '[^jz]' ;
    

  • Matches the end of words[[:>:]]:
    Gives all the titles ending with character “ack”. Example – Black.

    SELECT title FROM movies_tbl WHERE REGEXP 'ack[[:>:]]'; 
    

  • Matches the beginning of words[[:<:]]:
    Gives all the titles starting with character “for”. Example – Forgetting Sarah Marshal.

    SELECT title FROM movies_tbl WHERE title REGEXP '[[:<:]]for'; 
    

  • Matches a character class[:class:]:
    i.e [:lower:]- lowercase character ,[:digit:] – digit characters etc.
    Gives all the titles containing alphabetic character only. Example – stranger things, Avengers.

    SELECT title FROM movies_tbl WHERE REGEXP '[:alpha:]' ;
    




My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.



Improved By : ShubhamMaurya3



Article Tags :
Practice Tags :


3


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.