In this tutorial, we will learn about the REGEXP_LIKE() function which is used to match the given value with the specified regular expression. So, let’s get started!
What is a Regular Expression?
In your application, you will often need to find the result that matches a specific pattern. The LIKE operator is a good option to find the result but it is not as powerful as the regular expression.
The regular expression is a sequence made by the set of characters that specifies the search pattern.
Regular expressions are not language-specific as it does not depend on the syntax of a programming language. You can learn the regular expression and use it in almost every programing language.
It is a powerful application that can be used to find the records based on a specific pattern such as educational email ids, phone numbers as per the region, addresses etc.
MySQL provides us with the REGEXP_LIKE() function to use the regular expression to match it with the given attribute.
Introduction to REGEXP_LIKE() Function
As discussed earlier, the REGEXP_LIKE() function is used for pattern matching operations. It compares the given value with the specified regular expression and gives the result accordingly.
The function returns 1 if the given value matches with the pattern, else it returns 0. If you omit the regular expression or it is null, then the result is NULL.
The syntax for the pattern can be found here.
Also, you can specify the match_type argument which is an optional parameter. The match_type argument accepts the following characters-
- c: Case-sensitive match
- i: Case-insensitive match
- m: It refers to a multiple-line mode that acknowledges string line terminators. This function by default matches line terminators at both the beginning and end of the string.
- n: In order to match line terminators, it is utilised to change the. (dot) character. It defaults to stopping at the end of a line.
- u: solely Unix line ends. The match operators ., ^ and $ only accept the newline character as a line ending.
MySQL REGEXP_LIKE() Syntax
The following syntax shows how to use the function-
REGEXP_LIKE (expression, pattern [, match_type]) Code language: SQL (Structured Query Language) (sql)Here,
The expression is the actual value to match the pattern.
The pattern is the set of characters.
The match_type is an optional parameter to specify the match type as discussed above.
MySQL REGEXP_LIKE() Examples
First, let’s take simple examples to demonstrate the use of the function-
SELECT REGEXP_LIKE("journaldev","[A-Z]");Code language: SQL (Structured Query Language) (sql)Here, We are checking if the given string contains only alphabetical letters.
As you can see, we have not specified the case-sensitive matching character. Therefore, it does not matter if the given string contains small letters or capital letters. It will return 1.
Now, let’s try specifying the case-sensitive matching.
SELECT REGEXP_LIKE("journaldev","[A-Z]",'c');Code language: SQL (Structured Query Language) (sql)
As you can see, the function returns 0 because it does not contain any capital alphabet letter.
Now, we will check if the given string contains any digital.
SELECT REGEXP_LIKE("journaldev","[0-9]");
SELECT REGEXP_LIKE("journaldev10","[0-9]");Code language: SQL (Structured Query Language) (sql)Here, the first statement will return 0 because the given string does not contain any digit. On the other hand, it will return 1 for the second statement because it contains the digit.
As you can see, we have got the correct output.
In the next example, we will check if the given string starts with ‘j’ and ends with ‘v’ using the regular expression.
SELECT REGEXP_LIKE("journaldev","^j");
SELECT REGEXP_LIKE("journaldev","v$");Code language: SQL (Structured Query Language) (sql)
As you can see here, the string starts with j and ends with v, therefore we get the result 1.
Conclusion
In this tutorial, we have learned about the REGEXP_LIKE function which is used to match the string with the regular expression pattern. The possibility of using regular expressions is endless. Therefore, you can try as many as examples you want to have hands-on experience with regular expressions.
References
MySQL official documentation on the REGEXP_LIKE() function.