Back to Top

MySQL REGEXP Operator

MySQL Regular Expressions with The REGEXP Operator

MySQL is a relational database which provides many operators to handle the search inside the data.You can perform basic data retrieval filtering using the simple comparison operators in MySQL. REGEXP comes in mind while you want to perform complex pattern matching operation in MySQL.

What is Regular Expression?

A regular expression is a very useful tool that is used to match the patterns in the strings.Regular Expressions help to the matched search data from complex formulated string. To perform a pattern matching of a string against expression to find matched records is called regular expression. You can use regular expressions to search specific email, IP address, phone number or anything that has the specific pattern.

The regular expression in MySQL is known as REGEXP. REGEXP act as LIKE operator which allows to search strings by a full regular expression.The regular expression has its own syntax that can be interpreted by a regular expression processor. A regular expression is widely used in all programming languages to databases including MySQL.

MySQL Regular Expression Operators

REGEXP is the regular match operator for MySQL.MySQL provides standard SQL pattern matching syntax LIKE and also provides REGEXP for extended regular expression patterns. Using LIKE operator, You can match any single or multiple characters and also can use “%” matches any number of characters. You can perform another match using the REGEXP operator in MySQL. MySQL by default supports case insensitive schema.

MySQL introduced support for regular expressions using different functions.MySQL provides following operators for the regular expression operations.All are used in a WHERE clause instead of LIKE or comparison operator.

  1. REGEXP: The pattern matching operator for using regular expressions.
  2. NOT REGEXP: The negative option for the REGEXP operator.
  3. RLIKE: A synonym for the REGEXP operator.

MySQL Regular Expression Syntax

The basic syntax of MySQL regular expression is as follows:

Now, let’s check the syntax for other options

Regular expressions in MySQL are case sensitive but if you want to use both, you can use a character class to match two words. For example, “[aA]” matches the lowercase or uppercase “a” and “[a-zA-z]” matches any of the two words.

You can set the position of the pattern so that it match within the start or end of the value. You can use the “^” at the beginning of the pattern or “$” at the end of the pattern.

In this post, I will be talking about pattern matching Regexp operator in MySQL. Let’s start with a problem statement.

The above example match the name that begins with “a” as the “^” used to match the start of the name and “[aA]” to match the lowercase or Uppercase “a”:

A more complex example,

In above example, used REGEXP ‘(“”.*””)’ syntax which means it will apply regular expression search to the field name and find out the data which is quoted in “”. For example, This pattern will search data matched like “”bhumi”” or “”creativedev11″” or “”2017″”. You can write any pattern matching code as per your requirement to fetch the data.

Advantages:

The main advantage of regular expression in MySQL is that you can use the any of your own patterns to search for a string instead of MySQL LIKE operator which has fixed pattern for matching the string.

Disadvantages:

1. It is very difficult to understand for a beginner.
2. It decreases the performance of fetch query to retrieve data.

Do you use MySQL Regexp Operator in your application? For what kind of searches you are using MySQL Regexp Operator? Let me know in the comments below!

Like this post? Don’t forget to share it!

Share:
Share on Facebook0Share on LinkedIn0Tweet about this on TwitterShare on Google+0Buffer this pageDigg thisShare on Reddit0

Leave a Reply

Your email address will not be published. Required fields are marked *

Most Popular Posts

Tips to Choose Your Next WordPress Theme

Posted on 4 years ago

Bhumi

Two years blogiversary

Posted on 4 years ago

Bhumi

Facade implementation in Laravel

Updated 1 month ago

Bhumi

MySQL coalesce() function

Updated 18 years ago

Bhumi

How to use JavaScript Promise API?

Updated 18 years ago

Bhumi

What are Lambdas and Closures in PHP?

Updated 18 years ago

Bhumi

The Reader’s Poll – June 2015

Posted on 2 years ago

Bhumi

The Reader’s Poll – August 2014

Posted on 3 years ago

Bhumi

The Reader’s Poll – June 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – May 2014

Posted on 3 years ago

Bhumi

The Readers’ Poll – August 2013

Posted on 4 years ago

Bhumi