MySQL - RLIKE Operator

MySQL RLIKE Operator

The RLIKE operator in MySQL is used to search data in a database using patterns (or regular expressions), also known as pattern matching. In other words, the RLIKE operator is used to determine whether a given regular expression matches a record in a table or not. It returns 1 if the record is matched and 0, otherwise.

A regular expression is defined as a sequence of characters that represent a pattern in an input text. It is used to locate or replace text strings using some patterns; this pattern can either be a single/multiple characters or words, etc.

The functionally of this operator is equivalent to the MySQL REGEXP operator and is commonly used to search for specific patterns that meets certain criteria.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

expression RLIKE pattern

Patterns used with RLIKE

RLIKE operator is used with several patterns or regular expressions. Following is the table of patterns that can be used along with the this operator.

Example

The following example uses the RLIKE operator to retrieve records with the help of regular expressions. To do so, we are first creating a table named CUSTOMERS 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)
);

Now, insert some values into the above created table using the INSERT statements given below −

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 present in the CUSTOMERS table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

RLIKE with Patterns −

In the following query, we are finding all the records from CUSTOMERS table whose name starts with 'ch' −

SELECT * FROM CUSTOMERS WHERE NAME RLIKE '^ch';

Executing the query above will produce the following output −

The following query displays all the records whose names ends with 'sh' −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'sh$';

Following are records whose name ends with 'sh' −

Here, we are retrieving the records that have names containing 'an' −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE 'an';

Following are the records −

This following query retrieves all the records whose names are ending with an vowel −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '[aeiou]$';

Following are the records −

The below query finds all the names starting with a consonant and ending with 'ya' −

SELECT NAME FROM CUSTOMERS WHERE NAME RLIKE '^[^aeiou].*ya$';

As we observe the output, there are no records that starts with consonant and ends with 'ya'.

Empty set (0.00 sec)

RLIKE On Strings

The RLIKE operator can perform pattern matching not only on database tables but also on individual strings. Here, the result will obtain as 1 if the pattern exists in the given string, or 0 if it doesn't. The result is retrieved as a result-set using the SQL SELECT statement.

Syntax

Following is the basic syntax of the RLIKE operator in MySQL −

SELECT expression RLIKE pattern;

Example

In the following example, we are using the RLIKE query to check if a pattern exists in an individual string or not −

SELECT 'Welcome To Tutorialspoint!' RLIKE 'To';

The result-set will contain 1 because the pattern 'TO' exists in the specifed string.

Here, the pattern 'Hello' does not exist in the specifed string, thus it returns 0 as output.

SELECT 'Welcome To Tutorialspoint!' RLIKE 'Hello';

Executing the query above will produce the following output −

Example

REGEXP is alternative syntax to the RLIKE in MySQL. Both the operators have same result.

In the below query, if the given pattern is not found in the specifed string, this operator returns 0 −

SELECT 'Welcome to Tutorialspoint' REGEXP 'unknown';

Following is the output −

Here, the pattern 'is' does not exist in the specifed string, thus it returns 1 as output.

SELECT 'This is a sample string' REGEXP 'is';

Executing the query above will produce the following output −

Example

If either of the first two operands is NULL, the RLIKE operator returns NULL.

SELECT NULL RLIKE 'value';

Following is the output −

Here, the pattern we are searching is NULL, thus the output will also be NULL.

SELECT 'Tutorialspoint' RLIKE NULL;

Executing the query above will produce the following output −

Example

If you use the NOT clause before RLIKE operator, it returns 0 in case of a match else returns 1 (reverse of the original return values).

SELECT NOT 'This is a sample string' RLIKE 'is';

Following is the output −

Here, the pattern 'unknown' is not present in the specifed string, thus the following query returns 1 as output.

SELECT NOT 'Welcome to Tutorialspoint' REGEXP 'unknown';

Executing the query above will produce the following output −

RLIKE Operator Using a Client Program

We can also perform the MySQL RLike operator using the client programs to search data in a database using patterns (or regular expressions).

Syntax

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

$sql = "SELECT * FROM person_tbl WHERE NAME RLIKE 'sh$'";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME RLIKE 'sh$'";
con.query(sql);

String sql = "SELECT NAME FROM person_tbl WHERE NAME RLIKE '^sa'";
statement.executeQuery(sql);

sql = 'SELECT NAME FROM person_tbl WHERE NAME RLIKE '^sa''    
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 * FROM person_tbl WHERE NAME RLIKE 'sh$'";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Id %d, Name %s, Age %d, Address %s",
        $row['ID'],
        $row['NAME'],
        $row['AGE'],
        $row['ADDRESS']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
Id 3, Name Santosh, Age 34, Address Hyderabad
Id 6, Name Ramesh, Age 40, Address Mumbai

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 * FROM person_tbl WHERE NAME RLIKE 'sh$'";
 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:
[
  { ID: 3, NAME: 'Santosh', AGE: 34, ADDRESS: 'Hyderabad' },
  { ID: 6, NAME: 'Ramesh', AGE: 40, ADDRESS: 'Mumbai' }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RlikeOperator {
    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 NAME FROM person_tbl WHERE NAME RLIKE '^sa'";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String name = rs.getString("Name");
                System.out.println("Name: " + name);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}    

Table records: 
Name: Santosh

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()
rlike_operator_query = f"SELECT NAME FROM person_tbl WHERE NAME RLIKE '^sa'"
cursorObj.execute(rlike_operator_query)
result = cursorObj.fetchall()
print("Names that start with 'sa':")
for row in result:
    print(row[0])
cursorObj.close()
connection.close()    

Names that start with 'sa':
Santosh