MySQL Cookbook, 2nd Edition

Pattern Matching with Regular Expressions

Problem

You want to data type perform a pattern match rather than a literal comparison.

Solution

Use the REGEXP operator and a regular expression pattern, described in this section. Or use an SQL pattern, described in Pattern Matching with SQL Patterns.

Discussion

SQL patterns (see Pattern Matching with SQL Patterns) are likely to be implemented by other database systems, so they’re reasonably portable beyond MySQL. On the other hand, they’re somewhat limited. For example, you can easily write an SQL pattern %abc% to find strings that contain abc, but you cannot write a single SQL pattern to identify strings that contain any of the characters a, b, or c. Nor can you match string content based on character types such as letters or digits. For such operations, MySQL supports another type of pattern matching operation based on regular expressions and the REGEXP operator (or NOT REGEXP to reverse the sense of the match). REGEXP matching uses the pattern elements shown in the following table.

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
[...]	Any character listed between the square brackets
[^...]	Any character not listed between the square brackets
p1 | p2 | p3	Alternation; matches any of the patterns p1, p2, or p3
*	Zero or more instances of preceding element
+	One or more instances of preceding element
{ n }	n instances of preceding element
{ m , n }	m through n instances of preceding ...