PasswordSentry

Home

Using Regular Expressions to Search

mySQL, being the powerfull database system it is (and like programming languages including Perl, PHP, JavaScript, etc.), provides the capability to use regular expressions (regex) when selecting (or searching) data. This feature allows you to search for certain data elements, and limit your results. For example, only display data records for a given user. The basic regex syntax is as follows:

SELECT `value` FROM `tablename` WHERE `columnname` REGEXP 'regexp'

For example, to select all columns from the table, users, where the values in the column, date, end with 2015, execute the following mySQL query:

SELECT * FROM `users` WHERE `date` REGEXP '2015$'

The $ character is a metacharacter. Metacharacters instruct mySQL how to define the search. In this case, the $ metacharacter tells mySQL to select columns that end with 2015. Below you will see a list of the mySQL 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
[:alpha:] for letters
[:space:] for whitespace (space, tab, newline, and carriage return)
[:punct:] for punctuation
[:lower:] for lower case letters
[:upper:] for upper case letters
[abc] match one of enclosed chars
[^xyz] match any char not enclosed
| separates alternatives

Note that mySQL interprets a backslash () character as an escape character. To use a backslash in a regular expression, you must escape it with another backslash (\).

For a complete guide, check the MySQL Documentation.