MySQL REGEXP operator

Last update on August 19 2022 21:51:22 (UTC/GMT +8 hours)

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.

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: 5.6

Video Presentation

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

Sample 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)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-regexp-function - php mysql examples | w3resource</title>
<meta name="description" content="example-regexp-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of authors with all their detail available, where name of the author must begin with 'w':</h2>
<table class='table table-bordered'>
<tr>
<th>Author's ID</th><th>Author's name</th><th>Country</th><th>Home City</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";

$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT * FROM author WHERE aut_name REGEXP "^w"') as $row) {
echo "<tr>";
echo "<td>" . $row['aut_id'] . "</td>";
echo "<td>" . $row['aut_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['home_city'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example of REGEXP operator using(^) find from beginning in browser

JSP script

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-regexp-function</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT * FROM author WHERE aut_name REGEXP '^w'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Author's ID</td>
<td>Author's name</td>
<td>Country</td>
<td>Home City</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("aut_id")%></TD>
<TD><%=rs.getString("aut_name")%></TD>
<TD><%=rs.getString("country")%></TD>
<TD><%=rs.getString("home_city")%></TD>
</TR>
<%   }    %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Cant connect to database.");
}
%>
</body>
</html>

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

Sample 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

Sample 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)

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

Sample 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

Sample 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

Sample 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

Sample 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)

Online Practice Editor:

All String Functions

Previous: QUOTE
Next: REPEAT