MySQL REGEXP operator
REGEXP operator
MySQL REGEXP performs a pattern match of a string expression against a pattern. The pattern is supplied as an argument.
If the pattern finds a match in the expression, the function returns 1, else it returns 0.
If either expression or pattern is NULL, the function returns NULL.
This function is useful in -
- Pattern matching: It allows us to search for strings that match a specific pattern defined by a regular expression.
- Regular expression patterns: Meta-characters and quantifiers can be used to match characters, ranges of characters, or repeat characters in regular expressions.
- Complex pattern matching: Regular expressions allow you to define complex patterns that are difficult or impossible to express with simple wildcard characters (%) or underscores (_).
Syntax:
expr REGEXP pat
Argument:
Name | Description |
---|---|
expr | A string expression. |
pat | A pattern whose match is to be found in the expression. |
Note: As MySQL uses the C escape syntax in strings (for example, “\n” to represent the newline character), you must double any “\” that you use in your REGEXP strings. REGEXP is not case sensitive, except when used with binary strings.
MySQL Version: 8.0
MySQL REGEXP operator: Basic usage
The following MySQL statement returns all rows from the author table where the country column contains either 'USA' or 'UK'.
Code:
SELECT * FROM author WHERE country REGEXP 'USA|UK';
Sample table: author
Output:
aut_id|aut_name |country|home_city| ------+---------------+-------+---------+ AUT001|William Norton |UK |Cambridge| AUT003|William Anthony|UK |Leeds | AUT006|Thomas Merton |USA |New York | AUT007|Piers Gibson |UK |London | AUT008|Nikolai Dewey |USA |Atlanta | AUT010|Joseph Milton |USA |Houston | AUT014|C. J. Wilde |UK |London | AUT015|Butler Andre |USA |Florida |
Example of MySQL REGEXP operator using(^) find from beginning
The following MySQL statement will find the author’s name beginning with ‘w’. The ‘^’ is used to match the beginning of the name.
Code:
SELECT * FROM author
WHERE aut_name REGEXP '^w';
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP '^w'; +--------+-----------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+-----------------+---------+-----------+ | AUT001 | William Norton | UK | Cambridge | | AUT002 | William Maugham | Canada | Toronto | | AUT003 | William Anthony | UK | Leeds | +--------+-----------------+---------+-----------+ 3 rows in set (0.13 sec)
Example of MySQL REGEXP operator using (^) with binary operator
The following statement will find the author’s name beginning with ‘w’ exactly in lower case, because for case sensitivity BINARY operator has been used. Here no row has been fetched.
Code:
SELECT * FROM author
WHERE aut_name REGEXP BINARY '^w';
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP BINARY '^w'; Empty set (0.05 sec)
Example of MySQL REGEXP operator using ($) searching from the end
The following statement will find the author’s name ending with ‘on’. The ‘$’ character have been used to match the ending of the name.
Code:
SELECT *
FROM author
WHERE aut_name REGEXP "on$" ;
Sample table: author
Sample Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP "on$" ; +--------+----------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+----------------+---------+-----------+ | AUT001 | William Norton | UK | Cambridge | | AUT006 | Thomas Merton | USA | New York | | AUT007 | Piers Gibson | UK | London | | AUT010 | Joseph Milton | USA | Houston | +--------+----------------+---------+-----------+ 4 rows in set (0.00 sec)
Example of MySQL REGEXP operator searching within the string
The following statement will find the author’s name containing a ‘t’.
Code:
SELECT *
FROM author
WHERE aut_name REGEXP "t"
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP "t"; +--------+----------------------+-----------+-----------+ | aut_id | aut_name | country | home_city | +--------+----------------------+-----------+-----------+ | AUT001 | William Norton | UK | Cambridge | | AUT003 | William Anthony | UK | Leeds | | AUT004 | S.B.Swaminathan | India | Bangalore | | AUT005 | Thomas Morgan | Germany | Arnsberg | | AUT006 | Thomas Merton | USA | New York | | AUT010 | Joseph Milton | USA | Houston | | AUT011 | John Betjeman Hunter | Australia | Sydney | | AUT015 | Butler Andre | USA | Florida | +--------+----------------------+-----------+-----------+ 8 rows in set (0.00 sec)
Using REGEXP with character classes
The following MySQL statement returns true(1) or false(0)from the given expression where the text contains three consecutive digits
Code:
SELECT 'It12345contains3consicutive67digits' REGEXP '[0-9]{3}';
Output:
'It12345contains3consicutive67digits' REGEXP '[0-9]{3}'| -------------------------------------------------------+ 1|
Example of MySQL REGEXP operator searching specific character
The following statement will find the author’s name containing a ‘z’ or ‘v’ or ‘y’.
Code:
SELECT * FROM author
WHERE aut_name REGEXP "[zvy]";
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP "[zvy]"; +--------+-----------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+-----------------+---------+-----------+ | AUT003 | William Anthony | UK | Leeds | | AUT008 | Nikolai Dewey | USA | Atlanta | | AUT012 | Evan Hayek | Canada | Vancouver | +--------+-----------------+---------+-----------+ 3 rows in set (0.00 sec)
Example of MySQL REGEXP operator searching using a range of characters
The following statement will find the author’s name containing characters from ‘x’ to ‘z’.
Code:
SELECT * FROM author
WHERE aut_name REGEXP "[x-z]";
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP "[x-z]"; +--------+-----------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+-----------------+---------+-----------+ | AUT003 | William Anthony | UK | Leeds | | AUT008 | Nikolai Dewey | USA | Atlanta | | AUT012 | Evan Hayek | Canada | Vancouver | +--------+-----------------+---------+-----------+ 3 rows in set (0.00 sec)
Example of MySQL REGEXP operator searching a specific length of string
The following statement will find the author’s name containing exactly 12 characters. Use ‘^’ and ‘$’ match the beginning and ending of the name and twelve instances of ‘.’ have been used for maintaining twelve characters.
Code:
SELECT *
FROM author
WHERE aut_name REGEXP '^............$';
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP '^............$'; +--------+--------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+--------------+---------+-----------+ | AUT007 | Piers Gibson | UK | London | | AUT015 | Butler Andre | USA | Florida | +--------+--------------+---------+-----------+ 2 rows in set (0.01 sec)
Example of MySQL REGEXP operator searching a defined length of string
The following statement will find the author’s name containing exactly 12 characters. Use ‘^’ and ‘$’ match the beginning and ending of the name and ‘{12}’ have been after ‘.’ for repeating ‘.’ twelve times.
Code:
SELECT * FROM author
WHERE aut_name REGEXP '^.{12}$';
Sample table: author
Output:
mysql> SELECT * FROM author -> WHERE aut_name REGEXP '^.{12}$'; +--------+--------------+---------+-----------+ | aut_id | aut_name | country | home_city | +--------+--------------+---------+-----------+ | AUT007 | Piers Gibson | UK | London | | AUT015 | Butler Andre | USA | Florida | +--------+--------------+---------+-----------+ 2 rows in set (0.00 sec)
Video Presentation:
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook