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 Type	Examples	Description
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 \r	Literal new line, tab, return
	\cJ \cG	Control codes
	\xa3	Hex 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.

PHP Regex Cheat Sheet

Special Sequences

• \w - Any “word” character (a-z 0-9 _)
• \W - Any non “word” character
• \s - Whitespace (space, tab CRLF)
• \S - Any non whitepsace character
• \d - Digits (0-9)
• \D - Any non digit character
• . - (Period) – Any character except newline

Meta Characters

• ^ - Start of subject (or line in multiline mode)
• $ - End of subject (or line in multiline mode)
• [ - Start character class definition
• ] - End character class definition
• | - Alternates, eg (a|b) matches a or b
• ( - Start subpattern
• ) - End subpattern
• \ - Escape character

Quantifiers

• n* - Zero or more of n
• n+ - One or more of n
• n ? - Zero or one occurrences of n
• {n} - n occurrences exactly
• {n,} - At least n occurrences
• {,m} - At most m occurrences
• {n,m} - Between n and m occurrences (inclusive)

Pattern Modifiers

• i - Case Insensitive
• m - Multiline mode - ^ and $ match start and end of lines
• s - Dotall - . class includes newline
• x - Extended– comments and whitespace
• e - preg_replace only – enables evaluation of replacement as PHP code
• S - Extra analysis of pattern
• U - Pattern is ungreedy
• u - Pattern is treated as UTF-8

Point based assertions

• \b - Word boundary
• \B - Not a word boundary
• \A - Start of subject
• \Z - End of subject or newline at end
• \z - End of subject
• \G - First matching position in subject

Assertions

• (?=) - Positive look ahead assertion foo(?=bar) matches foo when followed by bar
• (?!) - Negative look ahead assertion foo(?!bar) matches foo when not followed by bar
• (?<=) - Positive look behind assertion (?<=foo)bar matches bar when preceded by foo
• (? - Negative look behind assertion (?
• (?>) - Once-only subpatterns (?>\d+)bar Performance enhancing when bar not present
• (?(x)) - Conditional subpatterns
• (?(3)foo|fu)bar - Matches foo if 3rd subpattern has matched, fu if not
• (?#) - Comment (?# Pattern does x y or z)

Download PHP Regex Cheat sheet