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

Sample table: author

Code:

-- This SQL query selects the name and country of authors
-- Explanation: The query retrieves information from the 'author' table, filtering out authors whose names start with the letter 'W'.
SELECT aut_name, country  -- Selecting specific columns aut_name and country from the 'author' table
FROM author  -- Specifying the table 'author' from which to retrieve the data
WHERE aut_name NOT LIKE 'W%';  -- Filtering the rows based on the author's name, selecting rows where the author's name does not start with 'W'

Explanation:

• The SELECT statement specifies the columns to be retrieved from the 'author' table: aut_name and country.


• The FROM clause specifies the table from which the data is to be retrieved, which is 'author'.


• The WHERE clause is used to filter the rows based on a condition.


• The NOT LIKE 'W%' condition filters out rows where the author's name starts with the letter 'W'. The % wildcard is used to match any sequence of characters following 'W'.

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)

Relational Algebra Expression:

Relational Algebra Tree:

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.

Sample table: author

Code:

-- This SQL query selects the name, country, and home city of authors
-- Explanation: The query retrieves information from the 'author' table, filtering out authors whose countries match specific patterns.
SELECT aut_name, country, home_city  -- Selecting specific columns aut_name, country, and home_city from the 'author' table
FROM author  -- Specifying the table 'author' from which to retrieve the data
WHERE country NOT LIKE 'U_A'  -- Filtering the rows based on the country column, excluding rows where the country matches the pattern 'U_A'
AND country NOT LIKE 'C__a_a';  -- Further filtering based on the country column, excluding rows where the country matches the pattern 'C__a_a'

Explanation:

• The SELECT statement specifies the columns to be retrieved from the 'author' table: aut_name, country, and home_city.


• The FROM clause specifies the table from which the data is to be retrieved, which is 'author'.


• The WHERE clause is used to filter the rows based on multiple conditions.


• The NOT LIKE 'U_A' condition filters out rows where the country matches the pattern 'U_A'. The underscore _ wildcard matches any single character, so 'U_A' matches any country with 'U' as the first character and 'A' as the third character.


• The AND operator combines two conditions, requiring both to be true for a row to be included.


• The NOT LIKE 'C__a_a' condition filters out rows where the country matches the pattern 'C__a_a'. The underscores _ match any single characters, so 'C__a_a' matches any country with 'C' as the first character, 'a' as the fourth character, and 'a' as the sixth character.

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)

Relational Algebra Expression:

Relational Algebra Tree:

Slideshow of MySQL Comparison Function and Operators

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