Does MYSQL supports PaTTerN ?????

“Regular Expression” often called as ‘pattern’ is an expression used to specify a set of strings required for a particular purpose. Many applications and programming languages have their own implementation of regular expressions, often with slight and sometimes with significant differences from other implementations.

Similarly, MYSQL also supports Regular  Expression. You must have seen MYSQL pattern matching with LIKE. But, MYSQL also supports another type of pattern matching operation based on regular expression i.e. REGEXP operator.

May be Question come to your mind. If MYSQL provide LIKE Operator for Pattern matching. Then, why I need REGEXP Operator. So, here your answer.

Why use REGEXP?
Because it has no boundaries, you can write your own patterns. As you are not only limited to search for a string based on any fixed pattern like MYSQL LIKE operator.

MYSQL provides three types of pattern opeartors:

• REGEXP (Pattern matching using regular expressions)
• NOT_REGEXP (Negation of REGEXP)
• RLIKE (Synonym for REGEXP)

Syntax of the Pattern operator:

SELECT column_name
 FROM table_name
 WHERE column_name REGEXP pattern,
 column_name NOT_REGEXP pattern;

Here, listed some patterns, can be used with REGEX Operator

Pattern	What the pattern matches
^	Beginning of string
$	End of string
.	Any single character
|	Separates alternatives
[…]	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} {n, }	m through n instances of preceding elementmatch n or more times
[:space:]	For Whitespace
[:upper:]	For upper case Letters
[:alpha:]	For Letters

MYSQL Regular Expression Example:
Consider we want to find out the names of students starts with N, B or T.

SELECT name, class, marks, age
 FROM students
 WHERE name REGEXP ‘^(N|B|T)’;

Next, let’s consider we need to find out names of students starting with vowel and end with ‘r’

SELECT name, class, marks, age
 FROM students
 WHERE name REGEXP ‘^[aeiou] | r$’;

I hope after reading you will get clear idea about Regular Expression in MYSQL.
Enjoy Coding! 🙂

Reference: http://dev.mysql.com/doc/refman/5.1/en/regexp.html#operator_regexp