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: