w3resource
MYSQL Tutorial

MySQL not like operator

not like operator

MySQL NOT LIKE operator along with WILDCARDS checks whether a string of a specified pattern is not present within another string.

Syntax:

NOT LIKE pat

Argument

Name Description
pat A pattern not to be matched.

Video Presentation

Example of MySQL not like operator with wildcard ( _ ) underscore

Sample table: author


Code:

SELECT * FROM author 
WHERE aut_name NOT LIKE '_______________'; 

Explanation

The above MySQL statement will return those rows from the table author in which the length of the author’s name is not exactly 15 characters. Fifteen instances of ‘_’ pattern character has been used to indicate 15 characters.

Sample Output:

mysql> SELECT * FROM author 
    -> WHERE aut_name NOT LIKE '_______________';
+--------+----------------------+-----------+----------------+
| aut_id | aut_name             | country   | home_city      |
+--------+----------------------+-----------+----------------+
| AUT001 | William Norton       | UK        | Cambridge      | 
| AUT005 | Thomas Morgan        | Germany   | Arnsberg       | 
| AUT006 | Thomas Merton        | USA       | New York       | 
| AUT007 | Piers Gibson         | UK        | London         | 
| AUT008 | Nikolai Dewey        | USA       | Atlanta        | 
| AUT009 | Marquis de Ellis     | Brazil    | Rio De Janerio | 
| AUT010 | Joseph Milton        | USA       | Houston        | 
| AUT011 | John Betjeman Hunter | Australia | Sydney         | 
| AUT012 | Evan Hayek           | Canada    | Vancouver      | 
| AUT013 | E. Howard            | Australia | Adelaide       | 
| AUT014 | C. J. Wilde          | UK        | London         | 
| AUT015 | Butler Andre         | USA       | Florida        | 
+--------+----------------------+-----------+----------------+
12 rows in set (0.00 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-not-like-function  php-mysql examples | w3resource</title>
<meta name="description" content="example-not-like-function  php-mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of authors with all their detail available, where name of the author does not contain exactly fifteen characters:</h2>
<table class='table table-bordered'>
<tr> 
<th>Author's ID</th><th>Author's name</th><th>Country</th><th>Home City</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT * FROM author WHERE aut_name NOT LIKE "_______________"') as $row) {
echo "<tr>";
echo "<td>" . $row['aut_id'] . "</td>";
echo "<td>" . $row['aut_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['home_city'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example of MySQL not like operator with wildcard (%)

Sample table: author


Code:

SELECT * FROM author 
WHERE aut_name NOT LIKE '%t%';

Explanation

The above MySQL statement will return those rows from the table author in which the author’s name does not containing a character ‘t’.

Sample Output:

mysql> SELECT * FROM author 
    -> WHERE aut_name NOT LIKE '%t%';
+--------+------------------+-----------+----------------+
| aut_id | aut_name         | country   | home_city      |
+--------+------------------+-----------+----------------+
| AUT002 | William Maugham  | Canada    | Toronto        | 
| AUT007 | Piers Gibson     | UK        | London         | 
| AUT008 | Nikolai Dewey    | USA       | Atlanta        | 
| AUT009 | Marquis de Ellis | Brazil    | Rio De Janerio | 
| AUT012 | Evan Hayek       | Canada    | Vancouver      | 
| AUT013 | E. Howard        | Australia | Adelaide       | 
| AUT014 | C. J. Wilde      | UK        | London         | 
+--------+------------------+-----------+----------------+
7 rows in set (0.00 sec)

PHP script

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example1-not-like-function  php-mysql examples | w3resource</title>
<meta name="description" content="example1-not-like-function  php-mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of authors with all their detail available, where name of the author does not contain 't':</h2>
<table class='table table-bordered'>
<tr>
<th>Author's ID</th><th>Author's name</th><th>Country</th><th>Home City</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT * FROM author WHERE aut_name NOT LIKE "%t%"') as $row) {
echo "<tr>";
echo "<td>" . $row['aut_id'] . "</td>";
echo "<td>" . $row['aut_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['home_city'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

All String Functions

MySQL String Functions, slide presentation

Many of our valued users post comments along with piece of code. Disqus may, false positively, consider those piece of code as Bad or Starnge syntax and send those comments to spam automatically. Even if that happens, we will make sure those useful comments are taken out of spam and approved. It may take a couple of days for that though, but any useful comment will be brought to public view for sure. We regret if that happend to any user. You may write us directly regarding this to - w3resource[at]yahoo[dot]com.