MySQL NOT REGEXP Operator

Technically, 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 character, multiple characters or words, etc.

In MySQL, the REGEXP operator is a powerful tool to perform complex search operations in a database to retrieve required data using regular expressions. And unlike the LIKE operator, the REGEXP operator is not restricted on search patterns (like % and _), as it uses several other meta characters to expand the flexibility and control during pattern matching.

NOT REGEXP Operator is a negation to this REGEXP operator. It is used to retrieve all the records present in a database that do not satisfy the specified pattern. Let us learn about this operator in detail further in this chapter.

Syntax

Following is the basic syntax of the MySQL NOT REGEXP operator −

expression NOT REGEXP pattern

Note − Since this is just a negation, the functionality of a NOT REGEXP operator will be the same as a REGEXP operator.

Examples

Let us start by 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)
);

The below INSERT statement 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 present in CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us show the usage of NOT REGEXPM operator using several queries on this table.

Query to find all the names not starting with ”ra” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''^ra

Following is the output −

Query to retrieve all the records whose names not ending with ”ik” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''ik$

Following is the output −

Following is the query to find all the names that do not contain ”an” −

SELECT * FROM CUSTOMERS WHERE NAME NOT REGEXP ''an

Following is the output −

Example

The NOT RLIKE is alternative syntax to the NOT REGEXP in MySQL. Both the operators have same result. If either of the first two operands is NULL, this operator returns NULL.

In the below query, the string value is NULL. Thus it gives output as NULL.

SELECT NULL NOT RLIKE ''value

Following is the output −

Here, the specified pattern is NULL. So, the output will be retrieved as NULL.

SELECT ''Tutorialspoint'' NOT REGEXP NULL;

Following is the output −

NOT REGEXP Operator Using a Client Program

Besides using MySQL queries to perform the NOT REGEXP operator, 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 * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
$mysqli->query($sql);

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
con.query(sql);

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

sql = "SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^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 NOT REGEXP ''^sa''";
if($result = $mysqli->query($sql)){
    printf("Table records(where the name does not start with ''sa''): 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(where the name does not start with ''sa''):
ID 1, Name John, Age 33, Address New York
ID 2, Name Ram, Age 29, Address Pune
ID 4, Name Tanya, Age 26, Address Paris
ID 5, Name Anmol, Age 28, Address Surat
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 NOT REGEXP ''^sa''";
 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: 1, NAME: ''John'', AGE: 33, ADDRESS: ''New York'' },
  { ID: 2, NAME: ''Ram'', AGE: 29, ADDRESS: ''Pune'' },
  { ID: 4, NAME: ''Tanya'', AGE: 26, ADDRESS: ''Paris'' },
  { ID: 5, NAME: ''Anmol'', AGE: 28, ADDRESS: ''Surat'' },
  { 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 regexp_not {
    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 * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
                String id = rs.getString("id");
                String name = rs.getString("Name");
                String age = rs.getString("AGE");
                String address = rs.getString("ADDRESS");
                System.out.println("Id: " + id + ", Name: " + name + ", Age: " + age + ", Address: " + address);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Table records:
Id: 1, Name: John, Age: 33, Address: New York
Id: 2, Name: Ram, Age: 29, Address: Pune
Id: 4, Name: Tanya, Age: 26, Address: Paris
Id: 5, Name: Anmol, Age: 28, Address: Surat
Id: 6, Name: Ramesh, Age: 40, Address: Mumbai

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()
notregexp_operator_query = f"SELECT * FROM person_tbl WHERE NAME NOT REGEXP ''^sa''"
cursorObj.execute(notregexp_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Persons whose name does not start with ''sa'' using NOT REGEXP:")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Persons whose name does not start with ''sa'' using NOT REGEXP:
(1, ''John'', 33, ''New York'')
(2, ''Ram'', 29, ''Pune'')
(4, ''Tanya'', 26, ''Paris'')
(5, ''Anmol'', 28, ''Surat'')
(6, ''Ramesh'', 40, ''Mumbai'')

MySQL NOT LIKE Operator

We have previously learned that the LIKE Operator in MySQL database is a logical operator used to perform pattern matching operation on a database table. And NOT LIKE Operator is defined as opposite of this LIKE operator.

Both LIKE and NOT LIKE operators perform pattern matching in a database table. Thus, they both need wildcards and patterns to function. However, if the LIKE operator is used to find the similar patterns mentioned using the wildcards, NOT LIKE operator is used to find all the records that do not contain the specified pattern.

• The NOT LIKE operator is nothing but the amalgamation of two SQL operators, NOT and LIKE operators. Thus, having the combination of their functionalities.

• It is used to match a particular pattern in the given string and returns 0 in case of a match and returns 1 otherwise. If either of the two operands of this function is NULL, it returns NULL as result.

• This operator is useful for finding strings that do not match a specific pattern or do not have certain characteristics.

Syntax

Following is the basic syntax of MySQL NOT LIKE operator with a SELECT statement −

SELECT column_name(s) FROM table_name
WHERE column_name NOT LIKE [condition];

Using NOT LIKE Operator with Wildcards

Wildcards are special characters used in SQL queries to match patterns in the data. Following wildcards can be used in conjunction with the NOT LIKE operator −

Note: In the NOT LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”.

Example

Let us begin by 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)
);

Using the below INSERT statements, we are inserting 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 below query to display all the records present in the CUSTOMERS table −

Select * from CUSTOMERS;

Following is the CUSTOMERS table −

Now, let us use the MySQL NOTLIKE operator to displays the all the records in CUSTOMERS table whose name doesn”t starts with ”k”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''k%

Following are the records whose name doesn”t starts with ”k” −

The following query displays the records of customers whose NAME doesn”t end with ”ik”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%ik

Following are the records whose name doesn”t ends with ”ik” −

Here, we are displaying all the records whose name does not contains the substring ”al”.

SELECT * FROM CUSTOMERS where NAME NOT LIKE ''%al%

Following are the records whose name doesn”t contains the substring ”al” −

The following query displays all the records whose name does not starts with ”m” and ends with ”y”.

SELECT * FROM CUSTOMERS WHERE NAME NOT LIKE ''m___y

As we can see in the output table, the seventh record is eliminated because the name starts with ”m” and ends with ”y”.

The below query displays all customer names that does not start with ”k” and have exactly 6 characters.

SELECT * FROM CUSTOMERS WHERE name NOT LIKE ''k_____

Following is the output −

Here, we are displaying the records of CUSTOMERS table, where the second character of the records in ADDRESS column is not “h”.

SELECT * FROM CUSTOMERS where ADDRESS NOT LIKE ''_h%

Following is the output −

Using NOT LIKE Operator with AND/OR Operators

We can use the MySQL NOT LIKE operator with different string patterns to choose rows, combining them with the AND or OR operators.

Syntax

Following is the syntax of using NOT LIKE operator with AND/OR operator −

SELECT column_name(s)
FROM table_name
WHERE column1 NOT LIKE pattern1 [AND|OR] column2 NOT LIKE pattern2 [AND|OR] ...;

Example

In the following example, we are displaying all records from the CUSTOMERS table where name does not start with ”k” and the address should not start with ”m” using AND operator −

SELECT * FROM CUSTOMERS
WHERE name NOT LIKE ''k%'' AND address NOT LIKE ''m%

Output

Executing the query above will produce the following output −

NOT LIKE Operator on Strings

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

Syntax

Following is the syntax of NOT LIKE operator in MySQL −

SELECT expression NOT LIKE pattern;

Example

In the following query, the pattern ”Tutorix” is not present in the specified string. So, this operator will return 1.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorix

Executing the query above will produce the following output −

Here, the pattern ”Tutorialspoint” is present in the specified string. Thus, it returns 0 as output.

SELECT ''Tutorialspoint'' NOT LIKE ''Tutorialspoint

Following is the output −

Example

If either (string or pattern operands) is NULL, this operator returns NULL. In the following query, the string is NULL, so that the output will be returned as NULL.

SELECT NULL NOT LIKE ''value

Executing the query above will produce the following output −

Here, the search pattern is NULL. So, the output will be returned as NULL.

SELECT ''Tutorialspoint'' NOT LIKE NULL;

Following is the output −

NOT LIKE Operator Using a Client Program

Besides using MySQL queries to perform the Not Like operator, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

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

$sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
$mysqli->query($sql);

sql = "SELECT * FROM tutorials_tbl where tutorial_author Not like ''Jo%''";
con.query(sql);

String sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''";
statement.executeQuery(sql);

sql = "SELECT * FROM EMP where Name NOT LIKE ''Su%''"
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 EMP where Name NOT LIKE ''Su%''";
if($result = $mysqli->query($sql)){
    printf("Table records: n");
    while($row = mysqli_fetch_array($result)){
        printf("ID %d, Name %s, DOB %s, Location %s",
        $row[''ID''],
        $row[''Name''],
        $row[''DOB''],
        $row[''Location''],);
        printf("n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();

Table records:
ID 101, Name Amit, DOB 1970-01-08, Location Hyderabad
ID 0, Name Raja, DOB 1980-11-06, Location Goa
ID 109, Name Javed, DOB 1980-11-06, Location pune
ID 120, Name Vani, DOB 1980-11-06, Location Delhi
ID 0, Name Devi, DOB 1980-11-06, Location Goa

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 tutorials_tbl where tutorial_author Not like ''Jo%''";
 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:
[
  {
    tutorial_id: 2,
    tutorial_title: ''Angular Java'',
    tutorial_author: ''Abdul S'',
    submission_date: 2023-08-07T18:30:00.000Z
  },
  {
    tutorial_id: 3,
    tutorial_title: ''Learning Java'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-05T18:30:00.000Z
  },
  {
    tutorial_id: 4,
    tutorial_title: ''Python Tutorial'',
    tutorial_author: ''Sasha Lee'',
    submission_date: 2016-09-03T18:30:00.000Z
  },
  {
    tutorial_id: 5,
    tutorial_title: ''Hadoop Tutorial'',
    tutorial_author: ''Chris Welsh'',
    submission_date: 2023-08-07T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class NotLikeOperator {
   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 * FROM EMP where Name NOT LIKE ''Su%''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
               String id = rs.getString("id");
               String name = rs.getString("Name");
               String dob = rs.getString("dob");
               String location = rs.getString("location");
               System.out.println("Id: " + id + ", Name: " + name + ", Dob: " + dob + ", Location: " + location);
            }
      }catch(Exception e) {
         e.printStackTrace();
      }
   }
}

Table records:
Id: 101, Name: Amit, Dob: 1970-01-08, Location: Hyderabad
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: MyID2, Name: Raja, Dob: 1980-11-06, Location: Goa
Id: 109, Name: Javed, Dob: 1980-11-06, Location: pune
Id: 120, Name: Vani, Dob: 1980-11-06, Location: Delhi
Id: oo1, Name: Devi, Dob: 1980-11-06, Location: Goa

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()
notlike_operator_query = f"SELECT * FROM EMP where Name NOT LIKE ''Su%''"
cursorObj.execute(notlike_operator_query)
# Fetching all the results
results = cursorObj.fetchall()
# display the results
print("Employees whose name does not start with ''Su'':")
for row in results:
    print(row)
cursorObj.close()
connection.close()

Employees whose name does not start with ''Su'':
(''101'', ''Amit'', datetime.date(1970, 1, 8), ''Hyderabad'')
(''MyID2'', ''Raja'', datetime.date(1980, 11, 6), ''Goa'')
(''109'', ''Javed'', datetime.date(1980, 11, 6), ''pune'')
(''120'', ''Vani'', datetime.date(1980, 11, 6), ''Delhi'')
(''oo1'', ''Devi'', datetime.date(1980, 11, 6), ''Goa'')

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

MySQL supports various types of pattern matching operations to retrieve filtered result-sets from huge database tables. In previous chapters, we have already learned about the LIKE operator for pattern matching. In this chapter, we will see another pattern matching operation based on regular expressions.

MySQL Regular Expressions

A regular expression is loosely 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 character, multiple characters or words, etc.

MySQL implements regular expression support using International Components for Unicode (ICU), which provides full Unicode support and is multi-byte safe.

In MySQL, it is a powerful way to perform a complex search operations in a database to retrieve desired content. And unlike the LIKE operator, the regular expressions are not restricted on search patterns (like % and _) as they use several other meta characters to expand the flexibility and control during pattern matching. This is performed using the REGEXP operator.

Syntax

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

expression REGEXP pattern

Patterns used with REGEXP

Following is the table of pattern, which can be used along with the REGEXP operator.

Examples

The following example demonstrates the usage of some patterns mentioned in the table above, along with the REGEXP operator. For that, we are first creating a database table to perform the search on.

Assume we are creating a table called 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 it 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 above created table −

SELECT * FROM CUSTOMERS;

Following are the records present in CUSTOMERS table −

REGEXP with Patterns −

Now, we are finding all the records in the CUSTOMERS table whose name starts with ”k” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^k

Executing the query above will produce the following output −

The following query retrieves all records in CUSTOMERS table whose name ends with ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh$

Executing the query above will produce the following output −

Here, we are retrieving all the records whose name contain ”sh” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''sh

As we can see the output, there are only two names that contain ”sh”.

In the following query, we are finding all the names starting with a vowel and ending with ”ol” −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[aeiou].*ol$

It returned an empty set because the CUSTOMERS table do not have any names who starts with vowel and ends with ”ol”

Empty set (0.00 sec)

The following query finds all the names in the CUSTOMERS table whose name starts with a consonant −

SELECT * FROM CUSTOMERS WHERE NAME REGEXP ''^[^aeiou]

Executing the query above will produce the following output −

Regular Expression Functions and Operators

Following is the table of functions and operators of regular expressions.

The MySQL SET data type

The MySQL SET data type is used to store a set of values chosen from a predefined list of values. Each column of the SET datatype can have zero or more values chosen from its list of values. These values are specified as a comma-separated list when inserting or updating data.

It is important to note that the list of values that can be stored in a SET column is defined at the time the table is created, and any values outside this list are not allowed.

For example, if we define a SET column like this −

test_col SET(''one'', ''two'') NOT NULL

The possible values for this column are −

• An empty string (””)
• ”one”
• ”two”
• ”one,two”

Storage of SET Data Type

A MySQL SET column can hold a maximum of 64 distinct members, which means that duplicate values are not allowed. If duplicates exist, MySQL will generate an error or a warning when strict SQL mode is enabled. Additionally, MySQL automatically removes trailing spaces from SET values when creating a table.

In MySQL, when you store a number in a SET column, the bits set in the binary representation of that number determine which set members are included in the column value. Consider the following query for a better understanding −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

In the above query, each set member is assigned a single bit with corresponding decimal and binary values −

So, if a value of 3 is assigned to the column (binary: 0011), it selects the first two SET members, resulting in ”Goa,Assam”.

Example

First of all, let us create a table with the name test_table using the following query −

Create table test_table (
   ID int auto_increment primary key ,
   COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
);

Following is the output obtained −

Query OK, 0 rows affected (0.02 sec)

When inserting values into a SET column, there is no specific order required for listing the elements. Even if a particular element is listed multiple times, when retrieved later, each element will appear only once, following the order specified during table creation.

Here, we are inserting the values into the set −

INSERT INTO test_table (COL1) VALUES
(''Goa,Assam''),
(''Assam,Goa''),
(''Goa,Assam,Goa''),
(''Goa,Assam,Assam''),
(''Assam,Goa,Assam'');

Output

The output produced is as shown below −

Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Verification

Let us display all the records of the ”test_table” using the SELECT statement as follows −

SELECT * FROM test_table;

As we can see the output below, all the values in ”COL1” will appear as ”Goa,Assam” −

Example

In the following query, we are searching for the SET values in the table using the MySQL LIKE operator. It finds rows where ”COL1” contains ”GOA” anywhere, even as a substring −

SELECT * FROM test_table WHERE COL1 LIKE ''%Goa%

Output

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

Example

In here, we are fetching the rows where the values are exactly ”Goa,Assam” and in the same order as listed in the ”COL1” definition −

SELECT * FROM test_table WHERE COL1 = ''Goa,Assam

Output

The output for the above query is as given below −

Updating the SET Values

In MySQL, you can update SET elements in various ways: by replacing elements, adding elements, or removing elements from the SET data. Here are examples of each method −

Replacing SET Data

In the following query, we replace the value in the 5^th row with the number 11, which corresponds to Goa + Assam + Kerala (8 + 2 + 1) −

UPDATE test_table SET COL1 = 11 WHERE Id = 5;

Output

The query executes successfully and produces the following output −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

Following is the output produced −

Adding Data to SET

You can add elements to an existing SET column using the CONCAT() function. In this example, we add “Kerala” to the value in the 3rd row −

UPDATE test_table SET COL1 = CONCAT(COL1, ",Kerala")
WHERE Id = 3;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

To verify the changes done in the test_table, use the following SELECT query −

SELECT * FROM test_table;

The result shows the updated value −

Removing Data from SET

To remove a specific SET element, you can use the & ~ bitwise operation. In this example, we remove the “Assam” element from the value in the 4th row −

UPDATE test_table SET COL1 = COL1 & ~2 WHERE ID = 4;

Output

The output for this query is as follows −

Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Verification

Let us verify the test_table using the below query −

SELECT * FROM test_table;

Following is the table obtained −

SET Datatype Using a Client Program

We can also create column of the SET datatype using the client program.

Syntax

$sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
$mysqli->query($sql);

sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
con.query(sql);

String sql = "CREATE TABLE test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
statement.execute(sql);

sql = ''CREATE TABLE test_table (ID INT AUTO_INCREMENT PRIMARY KEY,  COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala''))''
cursorObj.execute(sql)

Example

Following are the programs −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s
", $mysqli->connect_error);
    exit();
}
//printf(''Connected successfully.
'');

$sql = ''Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala") )
$result = $mysqli->query($sql);
if ($result) {
    printf("Table created successfully...!n");
}
// insert data into created table
$q ="INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
if ($res = $mysqli->query($q)) {
    printf("Data inserted successfully...!n");
}
//now display the table records
$s = "SELECT * FROM test_table";
if ($r = $mysqli->query($s)) {
    printf("Table Records: n");
    while ($row = $r->fetch_assoc()) {
        printf(" ID: %d, Col_1: %s", $row["ID"], $row["COL1"]);
        printf("n");
    }
} else {
    printf(''Failed'');
}
$mysqli->close();

Table created successfully...!
Data inserted successfully...!
Table Records:
 ID: 1, Col_1: Goa,Assam
 ID: 2, Col_1: Goa,Assam
 ID: 3, Col_1: Goa,Assam
 ID: 4, Col_1: Goa,Assam
 ID: 5, Col_1: Goa,Assam

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

   //create a test_table table, that accepts one column of set type.
   sql = "Create table test_table (ID INT auto_increment primary key, COL1 set(''Goa'', ''Assam'', ''Delhi'', ''Kerala'') )";
   con.query(sql);

   //insert data into created table
   sql = "INSERT INTO test_table (COL1) VALUES (''Goa,Assam''), (''Assam,Goa''), (''Goa,Assam,Goa''), (''Goa,Assam,Assam''), (''Assam,Goa,Assam'')";
   con.query(sql);

   //select datatypes of salary
   sql = `SELECT DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = ''test_table'' AND COLUMN_NAME = ''col1''`;
   con.query(sql, function (err, result) {
     if (err) throw err;
     console.log(result);
   });
});

[ { DATA_TYPE: ''set'' } ]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class Set {
   public static void main(String[] args) {
      String url = "jdbc:mysql://localhost:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //Set data types...!;
         String sql = "Create table test_table (ID INT auto_increment primary key, COL1 set("Goa", "Assam", "Delhi", "Kerala"))";
         statement.execute(sql);
         System.out.println("column of a SET type created successfully...!");
         ResultSet resultSet = statement.executeQuery("DESCRIBE test_table");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+" "+resultSet.getString(2));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
}

Connected successfully...!
column of a SET type created successfully...!
ID int
COL1 set(''Goa'',''Assam'',''Delhi'',''Kerala'')

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()
# Create table with Set column
sql = ''''''
CREATE TABLE test_table (
ID INT AUTO_INCREMENT PRIMARY KEY,
COL1 SET(''Goa'', ''Assam'', ''Delhi'', ''Kerala'')
)
''''''
cursorObj.execute(sql)
print("The table is created successfully!")
# Data to be inserted
data_to_insert = [
    (''Goa,Assam'',),
    (''Assam,Goa'',),
    (''Goa,Assam,Goa'',),
    (''Goa,Assam,Assam'',),
    (''Assam,Goa,Assam'',)
]
# Insert data into the created table
insert_query = "INSERT INTO test_table (COL1) VALUES (%s)"
cursorObj.executemany(insert_query, data_to_insert)
# Commit the changes after the insert operation
connection.commit()
print("Rows inserted successfully.")
# Now display the table records
select_query = "SELECT * FROM test_table"
cursorObj.execute(select_query)
result = cursorObj.fetchall()
print("Test Table Data:")
for row in result:
    print(row)
cursorObj.close()
connection.close()

The table is created successfully!
Rows inserted successfully.
Test Table Data:
(1, {''Assam'', ''Goa''})
(2, {''Assam'', ''Goa''})
(3, {''Assam'', ''Goa''})
(4, {''Assam'', ''Goa''})
(5, {''Assam'', ''Goa''})

Many user applications require the storage of different types of data, including text, images, files, and more. Using BLOBs in a MySQL database allows you to store all these types of data within the same database, eliminating the need for a separate file system.

The MySQL BLOB Data Type

The MySQL BLOB (Binary Large Object) data type is used to store binary data, such as images, audio, video, or any other type of binary file. BLOB columns can store variable-length binary data, making it suitable for handling files of various sizes.

Consider an application that collects user information through forms. This information may include personal details, such as name and address, along with image proofs like PAN cards or AADHAR cards. Instead of managing these files separately in a file system, you can store them as BLOBs in a MySQL database.

Syntax

Following is the basic syntax to assign BLOB data type on a table field −

CREATE TABLE table_name (column_name BLOB,...)

Example

Let us consider a basic example to show how to assign BLOB datatype to a table field. Here, we are creating a table named ”demo_table” with two fields “ID” and “DEMO_FILE” −

CREATE TABLE demo_table (
   ID INT NOT NULL,
   DEMO_FILE BLOB
);

Following is the output obtained −

Query OK, 0 rows affected (0.01 sec)

You can see the table structure with the following command −

DESC demo_table;

The table obtained is as follows −

Inserting Data into BLOB Fields

You can insert some values into a database table, by loading a file to the BLOB field using the LOAD_FILE() function. However, before doing so, ensure that the following conditions are met −

• File Existence −The file you want to insert must exist on the MySQL server host location. To determine the required location, you can use the secure_file_priv variable with the following command. If the result of this command is not empty, the file to be loaded must be located in that specific directory.

SHOW VARIABLES LIKE secure_file_priv;