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:

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:

All String Functions (Slides presentation)

Previous: QUOTE
Next: REPEAT