MySQL Regular Expressions Cheat Sheet

MySQL Regular Expressions
Regular Expressions in MySQL are used within the REGEXP and RLIKE sections of WHERE clauses in the selection of records for display, update or deletion. They use Henry Spencer's implementation, which is aimed at conformance with POSIX standard 1003.2, extended version.

Operator TypeExamplesDescription
Literal Characters
Match a character exactly
a A y 6 % @Letters, digits and many special
characters match exactly
\$ \^ \+ \\ \?Precede other special characters
with a \ to cancel their regex special meaning
\n \t \rLiteral new line, tab, return
\cJ \cGControl codes
\xa3Hex codes for any character
Anchors and assertions ^Field starts with
$Field ends with
[[:<:]]Word starts with
[[:>:]]Word ends with
Character groups
any 1 character from the group
[aAeEiou]any character listed from [ to ]
[^aAeEiou]any character except aAeEio or u
[a-fA-F0-9]any hex character (0 to 9 or a to f)
.any character at all
[[:space:]]any space character (space \n \r or \t)
[[:alnum:]]any alphanumeric character (letter or digit)
Counts
apply to previous element
+1 or more ("some")
*0 or more ("perhaps some")
?0 or 1 ("perhaps a")
{4}exactly 4
{4,}4 or more
{4,8}between 4 and 8
Add a ? after any count to turn it sparse (match as few as possible) rather than have it default to greedy
Alternation |either, or
Grouping ( )group for count and save to variable

The above list shows the most commonly used elements of MySQL regular expressions, and is not exhaustive.

0 comments: