MySQL - REGEXP_SUBSTR() Function

Regular expressions in MySQL are used in search operations to filter and retrieve records from a database table that match specified patterns.

This process of detecting patterns in a set of data is known as pattern matching. It is helpful whenever the data is considered to have similar characteristics; in such cases, you might locate a pattern in the data and all its occurrences. Pattern matching is usually performed on raw data to make sure it is syntactically correct.

Similarly, in MySQL, pattern matching is performed to check whether the data is correctly stored or not. If not, the incorrect pattern is detected (and sometimes replaced) using functions of regular expressions. The regexp_substr() function is used to detect specified patterns from a set of data.

MySQL REGEXP_SUBSTR() Function

The MySQL regexp_substr() function is used for pattern matching in a database. This function returns the substring of a string that matches the pattern specified, NULL if either there is no match or, the string or the pattern is NULL. Here, a pattern is defined as an extended regular expression or just an ordinary string.

Syntax

Following is the syntax of the MySQL regexp_substr() function −

REGEXP_SUBSTR(expr, pattern[, pos[, occurrence[, match_type]]])

Parameters

The regexp_substr() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

This method also accepts following optional arguments −

• pos: Starting position of the search

• occurrence: Which occurrence of a match to replace. If omitted, the default is 1 so it retrieves the first occurrence only.

• match_type: A string that specifies how to perform matching.

Example

Following example shows the usage of MySQL regexp_substr() function on a simple string 'Welcome To Tutorialspoint!' using the SELECT statement as follows −

SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT;

On executing the given query, the output is displayed as follows −

If the pattern is not present in the string, the result is returned as NULL. Let us try to search for the pattern 'Hi' in the same string 'Welcome To Tutorialspoint!' using the following query −

SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'Hi') AS RESULT;

Following is the output −

Example

Let us pass 5 as value to the 'pos' parameter so the search starts from the 5th position in the given string; and as we are passing the occurrence value as 2, the second occurrence of the pattern 'to' after 5th position will be retrieved, irrespective of its case −

SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'To', 5, 2, 'i')
AS RESULT;

Output

When we execute the above query, the output is obtained as follows −

Example

If either of the first two arguments passed to this function is NULL, this function returns NULL. In the below query, we are passing NULL to the expression parameter.

SELECT REGEXP_SUBSTR(NULL, 'value');

On executing the given program, the output is displayed as follows −

Here, we are passing NULL as a pattern to search −

SELECT REGEXP_SUBSTR('Welcome to Tutorialspoint', NULL)
AS Result;

Following is the output −

Example

In the following query, we are performing a search operation on a database table named CUSTOMERS using the REGEXP_SUBSTR() function. Firstly, let us create the table using the query below −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Following query adds 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

Execute the following query to display all the records of CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are using the REGEXP_SUBSTR() function to retrieve the substring from the the NAME column values that begin with 'Ra'.

SELECT REGEXP_SUBSTR(NAME, '^Ra') AS RESULT FROM CUSTOMERS;

Output

As we can see the output below, only the first record in NAME column has a substring 'Ra' in it −

REGEXP_SUBSTR() Funcion Using a Client Program

We can also perform the MySQL REGEXP_SUBSTR() function using the client programs to detect specified patterns from a set of data.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
$mysqli->query($sql);

sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
con.query(sql);

String sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
statement.executeQuery(sql);

sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT" 
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.
');
$sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %s", $row['RESULT']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: We

var mysql = require('mysql2');
var con = mysql.createConnection({
host:"localhost",
user:"root",
password:"password"
});
 //Connecting to MySQL
 con.connect(function(err) {
 if (err) throw err;
  //console.log("Connected successfully...!");
  //console.log("--------------------------");
 sql = "USE TUTORIALS";
 con.query(sql);
 sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
 console.log("Select query executed successfully..!");
 console.log("Table records: ");
 con.query(sql);
 con.query(sql, function(err, result){
 if (err) throw err;
 console.log(result);
 });
});    

Select query executed successfully..!
Table records:
[ { RESULT: 'We' } ]    

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_substr {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT";
            rs = st.executeQuery(sql);
            while(rs.next()) {
                String result = rs.getString("RESULT");
                System.out.println("Result: " + result);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Result: We

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object
cursorObj = connection.cursor()
regexp_substr_query = f"SELECT REGEXP_SUBSTR('Welcome To Tutorialspoint!', 'We') AS RESULT"
cursorObj.execute(regexp_substr_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_SUBSTR() Function:")
for row in results:
    result = row[0]
    print(f"The extracted substring is: '{result}'")
cursorObj.close()
connection.close()       

Result of REGEXP_SUBSTR() Function:
The extracted substring is: 'We'