Regular Expressions in MySQL query

The internal engine of MySQL has native - in addition to the common operators of equality, inequality and similarity (LIKE) - a very useful operator: REGEXP operator.
As its name suggests, the REGEXP operator leverages the power of regular expressions to perform complex searches within our MySQL database. The syntax is simple:

 SELECT field_name







 FROM table_name







 WHERE field_name REGEXP expression;

 

Let's follow a simple summary table of the main characters and the special syntax used in the expression of comparison:

^	Beginning of the string
$	End of the string
.	Any character
[...]	Any of the characters in the brackets
[^...]	Any character except those in square brackets
|	Separate strings of characters or their alternatives
*	Zero or more repetitions of the previous character or the string
+	One or more repetitions of the previous character or the string
{N}	"N" character or the repetition of the previous string
{Min, max}	Repeats the previous character or string to a number of times between a minimum and a maximum

We make a few practical examples that, as usual, is worth a thousand words ...
For simplicity we will refer to a hypothetical calendar database containing the names of our friends.

1) We find all names starting with "m"

 



 SELECT name FROM calendar WHERE name REGEXP '^ m';

 

Our query will return, for example:

• M ara
• M arch
• M ary
• Ilena M

2) We find all names that end with "or"

 



 SELECT name FROM calendar WHERE name REGEXP 'or $';

 

Our query will return, for example:

• Or Philip
• Lights or
• Marc and
• Seas or
• Paul or

3) We find all names starting with "m" and eventually "or"

 



 SELECT name FROM calendar WHERE name REGEXP '^ m + o $';

 

Our query will return, for example:

• M arc or
• M ari or

4) We find all names that contain the letter "r"

 



 SELECT name FROM calendar WHERE name REGEXP 'r';

 

Our query will return, for example:

• R cla
• But r
• But r co
• But I r

5) We find all names that contain groups of letters "but" or "I"

 



 SELECT name FROM calendar WHERE name REGEXP 'ma | ra';

 

Our query will return, for example:

• I luc
• But ra
• But rco
• But I r

6) We find all names that do NOT contain the letters "m" and "a"

 



 SELECT name FROM calendar WHERE name REGEXP '[^ ma]';

 

Our query will return, for example:

• Philip
• Lucio

I leave the reader to further test the potential REGEXP operator, since the possible areas of use are so many and it is difficult to summarize in a short article as this is meant to be.