w3resource

MySQL NOT LIKE operator

NOT LIKE operator

MySQL NOT LIKE is used to exclude those rows which are matching the criterion followed by LIKE operator.

Syntax:

expr NOT LIKE pat [ESCAPE 'escape_char']
  • Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
  • The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
  • Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator.
  • LIKE operator uses WILDCARDS (i.e. %, _) to match the pattern. This is very useful to check whether a particular character or string is present in the records.

% is used to match any number of characters, even zero characters.
_ is used to match exactly one character.

To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.
\% is used to match one "%" character.
\_ Matches one "_" character

MySQL Version: 8.0

Example: MySQL NOT LIKE operator with (%) percent

The following MySQL statement excludes those rows from the table author, having the 1st character of aut_name ‘W’.

Code:

SELECT aut_name, country
FROM author 
WHERE aut_name NOT LIKE 'W%';

Relational Algebra Expression:

Relational Algebra Expression: MySQL NOT LIKE operator with (%) percent.

Relational Algebra Tree:

Relational Algebra Tree: MySQL NOT LIKE operator with (%) percent.

Sample table: author


Output:

mysql> SELECT aut_name, country
    -> FROM author 
    -> WHERE aut_name NOT LIKE 'W%';
+----------------------+-----------+
| aut_name             | country   |
+----------------------+-----------+
| S.B.Swaminathan      | India     | 
| Thomas Morgan        | Germany   | 
| Thomas Merton        | USA       | 
| Piers Gibson         | UK        | 
| Nikolai Dewey        | USA       | 
| Marquis de Ellis     | Brazil    | 
| Joseph Milton        | USA       | 
| John Betjeman Hunter | Australia | 
| Evan Hayek           | Canada    | 
| E. Howard            | Australia | 
| C. J. Wilde          | UK        | 
| Butler Andre         | USA       | 
+----------------------+-----------+
12 rows in set (0.00 sec)

Example : MySQL NOT LIKE operator with ( _ ) underscore

The following MySQL statement excludes those rows from the table author having the country name like the above pattern as specified with LIKE operator.

Code:

SELECT aut_name, country,home_city            
FROM author          
WHERE country NOT LIKE 'U_A' and country NOT LIKE  'C__a_a';

Relational Algebra Expression:

Relational Algebra Expression: MySQL NOT LIKE operator with ( _ ) underscore.

Relational Algebra Tree:

Relational Algebra Tree: MySQL NOT LIKE operator with ( _ ) underscore.

Sample table: author


Output:

mysql> SELECT aut_name, country,home_city            
    -> FROM author          
    -> WHERE country NOT LIKE 'U_A' and country NOT LIKE  'C__a_a';
+----------------------+-----------+----------------+
| aut_name             | country   | home_city      |
+----------------------+-----------+----------------+
| William Norton       | UK        | Cambridge      | 
| William Anthony      | UK        | Leeds          | 
| S.B.Swaminathan      | India     | Bangalore      | 
| Thomas Morgan        | Germany   | Arnsberg       | 
| Piers Gibson         | UK        | London         | 
| Marquis de Ellis     | Brazil    | Rio De Janerio | 
| John Betjeman Hunter | Australia | Sydney         | 
| E. Howard            | Australia | Adelaide       | 
| C. J. Wilde          | UK        | London         | 
+----------------------+-----------+----------------+
9 rows in set (0.00 sec)

Slideshow of MySQL Comparison Function and Operators

Previous: NOT IN()
Next: MySQL Logical Operators AND operator



Follow us on Facebook and Twitter for latest update.