MySQL Regexp Operator

MySQL Regexp Operator 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 Operator

REGEXP operator in MySQL is used for pattern matching. It compares the given pattern in the input string and returns the result which is matching with the patterns. If this operator finds a match, the result is 1. Otherwise, the result is 0. This operator works the same as the REGEXP_LIKE() function.

Syntax

The following is a basic syntax to use this operator in MySQL:

expression REGEXP pattern ;

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.

Let us understand how this operator works in MySQL through examples.

Example

The below statement is the most basic regular expression where we have not uses any special characters. It means we have just used a string and compare if any part of the input string matches the pattern, it returns a match.

mysql> SELECT 
  'Corner' REGEXP 'Corn' AS 'Match',
  'Cheese' REGEXP 'Corn' AS 'Not-Match';

Here is the result:

Suppose we have a table named employee that contains the following data. Now, we will demonstrate various examples based on this table data.

If we want to search for an employee whose name start with j or s, we can do this as follows:

mysql> SELECT * FROM employee WHERE Name REGEXP '^[js]';

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee whose name contains exactly four characters, we need to use the '^' and '$ meta-characters. These characters match the beginning and end of the employee name and repeat {4} times of any character '.' in-between as shown in the following statement:

mysql> SELECT * FROM employee WHERE Name REGEXP '^.{4}$';

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee detail whose designation contains 'er' characters, we can do this by using the below query:

mysql> SELECT * FROM employee WHERE Designation REGEXP 'er';

Executing the statement, we will get the desired result. See the below output:

If we want to get the employee name and designation whose name either contains p or s characters, we can do this by using the below query:

mysql> SELECT Name, Designation FROM employee WHERE Name REGEXP 'p|s';

Executing the statement, we will get the desired result. See the below output:

Next TopicMySQL regexp_instr() 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

MySQL Regexp Operator

MySQL REGEXP Operator

Syntax

Example

Related Links:

.Net

Java

Related Links