MySQL RLIKE Operator
The RLIKE operator in MySQL is used for pattern matching. It is used to determine whether the given strings match a regular expression or not. It returns 1 if the strings match the regular expression and return 0 if no match is found. This operator returns a similar result as the REGEXP_LIKE() function. Hence RLIKE operator is synonyms to the REGEXP_LIKE() function.
Syntax
The following is a basic syntax to use this operator in MySQL:
In this syntax, the expression is an input string on which we will perform searching for matching the regular expression. And pattern represents the regular expression for which we are testing the string.
This syntax is generally used with the SELECT statements as follows:
Let us understand how this operator works in MySQL through examples.
Example
The below statement is the most basic example of the RLIKE operator. Here we have just used a string and compare if any part of the input string matches the pattern to get the result.
Here is the result:
Suppose we have a table named student_info that contains the following data. We will demonstrate various examples based on this table data.
If we want to get the students name, subject, and marks whose subject ends with the “s or y” letter, we can get these details by using the statement as follows:
Executing the statement, we will get the desired result. See the below output:
The below statement is another example where we have uses regular expression character dot (.) that specifies there can be no characters, one character, or many characters in place of this:
Executing the statement, we will get the desired result. See the below output:
MySQL NOT RLIKE Operator
It is the negation of the RLIKE operator used for pattern matching. This operator determines whether a pattern is not present in the input string. It returns 0 if the strings do not match the regular expression and return 1 if the match is found. We can use this operator as follows:
The above operator is equivalent to the below syntax:
In this syntax, the expression is an input string on which we will perform searching for matching the regular expression. And pattern represents the regular expression for which we are testing the string.
This syntax is generally used with the SELECT statements as follows:
Let us understand how this operator works in MySQL through examples.
Example
The below statement is the most basic example of the NOT RLIKE operator. Here we have just used a string and compare if any part of the input string does not match the pattern.
We will get the below result. Here the pattern is matched because the input string starts with Java. Since we use the NOT RLIKE operator, it returns the negative result (0).
The above statement is the equivalent of doing this:
This is another example that gives the result 1. It indicates that the string doesn’t match with the given pattern.
Here is the output:
Let us see some other examples based on the above table student_info.
The below statement returns the students name, subject, and marks whose subject does not ends with the “s or y” letter:
Executing the statement, we will get the desired result. See the below output:
The below statement returns the student detail whose name does not contain exactly four characters:
Here we have uses ‘^’ and ‘$ meta-characters that matches the beginning and end of the student name, and repeat {4} times of any character ‘.’ in between. See the below output:
