MySQL - REGEXP_LIKE() Function

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. But, pattern matching with regular expressions is a powerful way to perform a complex search.

As we have seen in the previous chapter, the MySQL regexp_instr() function is used to return the position of the pattern found. But if you want to just detect whether the pattern is present in the data or not, you can use the regexp_like() function.

MySQL REGEXP_LIKE() Function

The MySQL regexp_like() function is also used to search for a string that is matched with specified patterns. This function returns 1 if this string matches the specified pattern, 0 if there is no match, or NULL if the string or the pattern is NULL. The pattern used in this function can be an extended regular expression and not just an ordinary string.

Syntax

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

REGEXP_LIKE(expr, pattern[, match_type])

Parameters

The regexp_like() function takes following parameter values −

• expr: The string in which search is performed

• pattern: The pattern that is searched in the string

• match_type: (Optional argument) A string that specifies how to perform matching; includes case-sensitive matching(c), case-insensitive matching(i), multiple-line mode(m), matching line terminators(n), matching Unix-only line endings(u).

Example

In this example, we are performing a search operation on a simple string using the MySQL REGEXP_LIKE() function −

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

The search pattern 'To' is present in the string, so it returned 1 as output.

Now, if there is no match found in the string, the result will be obtained as '0' as shown below −

SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'Hello') 
AS RESULT;

Following is the output −

Let us also pass the optional arguments to this function as case-sensitive matching(c) and observe the result −

SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 't', 'c') 
AS RESULT;

Executing the query above will produce the following output −

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 string parameter.

SELECT REGEXP_LIKE(NULL, 'value') AS Result;

Following is the output −

Here, we are passing NULL as the search pattern −

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

Executing the query above will produce the following output −

Example

In another example, let us perform a search operation on a database table named CUSTOMERS using the REGEXP_LIKE() function. Firstly, let us create the table using the following query −

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

The following query inserts 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 −

The following query selects records from the CUSTOMERS table where the NAME column starts with the letter 'K' −

SELECT REGEXP_LIKE(NAME, '^K') 
AS RESULT FROM CUSTOMERS;

If there is a name that starts with letter 'K' it gives 1 as output, else 0 −

The following query checks whether the 'ADDRESS' column in the 'CUSTOMERS' table contains the letter 'K' (case-insensitive). If the address contains 'K' or 'k,' the result is 1; otherwise, it's 0.

SELECT REGEXP_LIKE(ADDRESS, 'R', 'i') 
AS RESULT FROM CUSTOMERS;

As we can see in the output table, 6th and 7th row in ADDRESS column contains a letter 'K' (case-insensitive) −

REGEXP_LIKE() Function Using a Client Program

Besides using MySQL queries to perform the REGEXP_Like() function, we can also use client programs such as PHP, Node.js, Java, and Python to achieve the same result.

Syntax

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

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

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

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

sql = "SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'To') 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_LIKE('Welcome To Tutorialspoint!', 'To') AS RESULT";
if($result = $mysqli->query($sql)){
    while($row = mysqli_fetch_array($result)){
        printf("Result: %d", $row['RESULT']);
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Result: 1

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_LIKE('Welcome To Tutorialspoint!', 'To') 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: 1 } ]      

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class regexp_like {
    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_LIKE('Welcome To Tutorialspoint!', 'To') 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: 1

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_like_query = f"SELECT REGEXP_LIKE('Welcome To Tutorialspoint!', 'To') AS RESULT"
cursorObj.execute(regexp_like_query)
# Fetching all the results
results = cursorObj.fetchall()
# Display the result
print("Result of REGEXP_LIKE() Function:")
for row in results:
    result = row[0]
    if result:
        print("The pattern 'To' is found in the given string.")
    else:
        print("The pattern 'To' is not found in the given string.")
cursorObj.close()
connection.close()    

Result of REGEXP_LIKE() Function:
The pattern 'To' is found in the given string.