Khóa học miễn phí MySQL – NOT LIKE Operator nhận dự án làm có lương

MySQL – NOT LIKE Operator

Table of content


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 −

S.No WildCard & Definition

1

%

The percent sign represents zero, one or multiple characters.

2

_

The underscore represents a single number or character.

3

[]

This matches any single character within the given range in the [].

4

[^]

This matches any single character excluding the given range in the [^].

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 −

ID NAME AGE ADDRESS SALARY
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

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” −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

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” −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

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” −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

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”.

ID NAME AGE ADDRESS SALARY
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

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.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

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 −

ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

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 −

”Tutorialspoint” NOT LIKE ”Tutorix”
1

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 −

”Tutorialspoint” NOT LIKE ”Tutorialspoint”
0

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 −

NULL NOT LIKE ”value”
NULL

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

SELECT ''Tutorialspoint'' NOT LIKE NULL;

Following is the output −

”Tutorialspoint” NOT LIKE NULL
NULL

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 −

To find data in a MySQL database that doesn”t match a specific pattern using a PHP program, you can use the Not Like operator. To do this, we need to execute the ”SELECT” statement using the mysqli function query() as −

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

To find data in a MySQL database that doesn”t match a specific pattern using a Node.js program, you can use the Not Like operator. To do this, we need to execute the ”SELECT” statement using the query() function of the mysql2 library as −

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

To find data in a MySQL database that doesn”t match a specific pattern using a Java program, you can use the Not Like operator. To do this, we need to execute the ”SELECT” statement using the JDBC function executeUpdate() as −

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

To find data in a MySQL database that doesn”t match a specific pattern using a Python program, you can use the Not Like operator. To do this, we need to execute the ”SELECT” statement using the execute() function of the MySQL Connector/Python as −

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

Output

The output obtained is as follows −

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

Output

The output produced is as follows −

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

Output

The output obtained is as shown below −

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

Output

Following is the output of the above code −

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

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc