MySQL REGEXP_LIKE() Function
The REGEXP_LIKE() function in MySQL is used for pattern matching. It compares 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.
Syntax
The following is a basic syntax to use this function in MySQL:
Parameter Explanation
The explanation of the REGEXP_LIKE() function parameters are:
expression: It is an input string on which we perform searching for matching the regular expression.
pattern: It represents the regular expression for which we are testing the string.
match_type: It is a string that allows us to refine the regular expression. It uses the following possible characters to perform matching.
- c: It represents a case-sensitive matching.
- i: It represents a case-insensitive matching.
- m: It represents a multiple-line mode that recognizes line terminators within the string. By default, this function matches line terminators at the start and end of the string.
- n: It is used to modify the . (dot) character to match line terminators. By default, it will stop at the end of a line.
- u: It represents Unix-only line endings that recognize only the newline character by the ., ^, and $ match operators.
Let us understand how we can use this function in MySQL with various examples.
Example
The following statement explains the basic example of the REGEXP_LIKE function in MySQL.
In this example, the regular expression can specify any character in place of the dot. Therefore, we will get a match here. So this function returns 1 to indicate a match.
The below statement is another example where the input string does not match the given regular expression.
Here is the output:
The below statement is another example where the specified regular expression searches whether the string end with the given characters or not:
Here is the result:
We can provide an additional parameter to refine the regular expression by using the match type arguments. See the below example where we are specifying a case-sensitive and case-insensitive match:
Here is the result:
