I added a page with a reg expression cheat sheet, so I wanted to post how MySQL can use reg expressions in select queries. I use these and they can be powerful. Using reg expressions in selects is about as basic as it comes.

The format for reg expression for a SELECT is:

SELECT something FROM table WHERE column REGEXP 'regexp'

For example, to select all columns from the table events where the values in the column id end with 5309, use:

SELECT * FROM events WHERE id REGEXP '5309$'

A more elaborate example selects all columns of the table reviews where the values in the column description contain the word “mysql”:

SELECT * FROM reviews WHERE description REGEXP '[[:<:]]mysql[[:>:]]'

MySQL allows the following regular expression metacharacters:. match any character

• ? 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 (\\).