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