MySQL - NOT LIKE Operator

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