Regular Expressions in MySQL

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.

Back to Regular Expression Home Page
Jump to Elements of a regular expression
Order a Regular Expression Mousemat for £4.95 inclusive
Link to < a href=http://www.wellho.net/booktopic/Regular_expressions.html>books on Regular Expressions