MySQL - Regexps

You have seen MySQL pattern matching with LIKE ...%. MySQL supports another type of pattern matching operation based on the regular expressions and the REGEXP operator. If you are aware of PHP or PERL, then it is very simple for you to understand because this matching is same pke those scripting the regular expressions.

Following is the table of pattern, which can be used along with the REGEXP operator.

Examples

Now based on above table, you can device various type of SQL queries to meet your requirements. Here, I am psting a few for your understanding.

Consider we have a table called person_tbl and it is having a field called name −

Query to find all the names starting with st −

mysql> SELECT name FROM person_tbl WHERE name REGEXP  ^st ;

Query to find all the names ending with ok −

mysql> SELECT name FROM person_tbl WHERE name REGEXP  ok$ ;

Query to find all the names, which contain mar −

mysql> SELECT name FROM person_tbl WHERE name REGEXP  mar ;

Query to find all the names starting with a vowel and ending with ok −

mysql> SELECT FirstName FROM intque.person_tbl WHERE FirstName REGEXP  ^[aeiou].*ok$ ;