Introduction
A regular expression is a special string that describes a search pattern. The advantage of using regular expression is that you are not limited to search for a string based on a fixed pattern with the percent sign (%) and underscore (_) in the LIKE operator.
Syntax
Syntax of the REGEXP operator in the WHERE clause.
SELECT column_list FROM table_name WHERE column REGEXP pattern;
Above statement performs a pattern match of a column against a pattern. If a value in the column matches the pattern, the expression in the WHERE clause returns true, otherwise it returns false. If either column or pattern is NULL, the result is NULL.
RLIKE operator is the synonym of the REGEXP operator. Negation form of the REGEXP operator is NOT REGEXP.
Meta Characters
Meta characters are the building blocks of regular expressions. Characters in RegEx are understood to be either a meta character with a special meaning or a regular character with a literal meaning. Commonly used meta characters in regular expression are
- . : Matches any single character
- ^ : Matches the position at the beginning of string
- $ : Matches the position at the end of string
- […] : Matches any character specified inside the square brackets
- [^…] : Matches any character not specified inside the square brackets
- p1|p2 : Matches any of the patterns p1 or p2
- * : Matches preceding character zero or more times
- + : Matches preceding character one or more times
- {n} : Matches n number of instances of the preceding character
- {m,n} : Matches from m to n number of instances of the preceding character
Example
Below statement find all candidates from candidates table whose full name start with character P or R.
SELECT fullname FROM candidates WHERE fullname REGEXP '^(P|R)'