Regular Expression with MySQL – A very cool and powerful capability
A very cool and powerful capability in MySQL and other databases is the ability to incorporate regular expression syntax when selecting data. The regular expresion support in MySQL is extensive. This recipe reviews regular expression use in MySQL and lists the supported regular expression metacharacters.
The basic syntax to use regular expressions in a MySQL query is:
SELECT post_content FROM wp_posts WHERE post_content REGEXP ‘regexp’
For example, to select all columns from the table events where the values in the column id end with http, use:
A more elaborate example selects all columns of the table reviews where the values in the column description contain the word excellent:
MySQL allows the following regular expression metacharacters:
? match zero OR one
* match zero OR more
+ match one OR more
{n} match n times
{m,n} match m through n times
{n,} match n OR more times
^ beginning of line
$ end of line
[[:<:]] match beginning of words
[[:>:]] match ending of words
[:class:] match a character class
i.e., [:alpha:] FOR letters
[:space:] FOR whitespace
[:punct:] FOR punctuation
[:upper:] FOR upper case letters
[abc] match one of ENCLOSED chars
[^xyz] match any char NOT ENCLOSED
‘ separates alternatives
MySQL interprets a backslash (\) character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\\).
Very nice information. I was wondering about this, and your post explained what I needed to know. Thanks for this.