MySQL regexp_like() function

MySQL regexp_like() function for beginners and professionals on mysql tutorial, examples, functions, programming, mysql, literals, cursor, procedure, trigger, regexp_like(), regexp_replace operator, regular expression, regexp_instr(), crud etc.

<< Back to MYSQL

next → ← prev

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:

REGEXP_LIKE (expression, pattern [, match_type])

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.

mysql> SELECT REGEXP_LIKE ('England or America', 'l.nd') AS Result;

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.

mysql> SELECT REGEXP_LIKE ('MCA', 'BCA') AS Result;

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:

mysql> SELECT REGEXP_LIKE ('England Netherland Scotland', 'and$') AS Result;

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:

mysql> SELECT 
REGEXP_LIKE ('India Indonesia', '^in', 'i') AS 'Case-Insensitive',
REGEXP_LIKE ('India Indonesia', '^in', 'c') AS 'Case-Sensitive';

Here is the result:

Next TopicMySQL regexp_replace() Function

← prev next →

.Net

.NET Array Dictionary List String 2D Async DataTable Dates DateTime Enum File For Foreach Format IEnumerable If IndexOf Lambda LINQ Parse Path Process Property Regex Replace Sort Split Static StringBuilder Substring Switch Tuple

Java

Core Array ArrayList HashMap String 2D Cast Character Console Deque Duplicates File For Format HashSet If IndexOf Lambda Math ParseInt Process Random Regex Replace Sort Split StringBuilder Substring Switch Vector While

TheDeveloperBlog.com