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.
- REGEXP: The pattern matching operator for using regular expressions.
- NOT REGEXP: The negative option for the REGEXP operator.
- RLIKE: A synonym for the REGEXP operator.
MySQL Regular Expression Syntax
The basic syntax of MySQL regular expression is as follows:
1 2 3 | SELECT {fieldname} FROM {tablename} WHERE fieldname REGEXP 'pattern'; |
Now, let’s check the syntax for other options
1 2 3 4 5 6 | //For the NOT REGEXP Operator SELECT {fieldname} FROM {tablename} WHERE fieldname NOT REGEXP 'pattern'; //For the RLIKE Alias Operator SELECT {fieldname} FROM {tablename} WHERE fieldname RLIKE 'pattern'; |
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.
1 2 3 | SELECT * FROM table WHERE name REGEXP "^[aA]"; |
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,
1 2 3 | SELECT * FROM {table_name} WHERE {field_name} REGEXP '("".*"")' LIMIT 0 , 30 |
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!
