SQL Tutorial SQL Advanced SQL Database SQL References

SQL - NOT LIKE Operator



The SQL NOT LIKE operator is the negation of LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the LIKE or NOT LIKE operator are given below:

Wildcard Characters in MySQL

SymbolDescriptionExample
%Represents zero, one or multiple characters.'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.

Wildcard Characters in SQL Server

SymbolDescriptionExample
%Represents zero, one or multiple characters.'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
[]Represents any single character specified within [].'[ack]%' represents a value that starts with a, c or k, for example - ant, cat or kite etc.
-Represents a range of characters.'[a-d]%' represents a value that starts with a, b, c or d, for example - ant, bat, cat or dog etc.
[^]Represents any character not specified within [].'[^ack]%' represents a value that does not start with a, c or k, for example - bat, dog or rat etc.

Wildcard Characters in Oracle

SymbolDescriptionExample
%Represents zero, one or multiple characters.'J%' represents a value that start with "J", for example - John, Jo and Jack etc.
_Represents one character.'_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
ESCAPEAllow test for literal instances of a wildcard character such as % or _'J%!%' ESCAPE '!' represents a value that starts with J and ends in %, for example - John%, Jo% and Journey% etc.

Wildcard Characters in MS Access

SymbolDescriptionExample
*Represents zero, one or multiple characters.'J*' represents a value that start with "J", for example - John, Jo and Jack etc.
?Represents one character.'?o*' represents a value that have "o" in the second position, for example - John, Jo and Journey etc.
[]Represents any single character specified within [].'[ack]*' represents a value that starts with a, c or k, for example - ant, cat or kite etc.
-Represents a range of characters.'[a-d]*' represents a value that starts with a, b, c or d, for example - ant, bat, cat or dog etc.
[!]Represents any character not specified within [].'[!ack]*' represents a value that does not start with a, c or k, for example - bat, dog or rat etc.
#Represents any single numeric character.'5#5' represents a three digit number with 5 at one's and hundred's place, for example - 505, 555 or 595 etc.

Syntax

The syntax for using LIKE and NOT LIKE operators are given below:

/* Using LIKE operator */
SELECT column1, column2, ...
FROM table_name
WHERE column LIKE pattern;

/* Using NOT LIKE operator */
SELECT column1, column2, ...
FROM table_name
WHERE column NOT LIKE pattern;

The table below describes patterns which is used with LIKE operator and uses (%) and (_).

PatternDescription
'J%'A value that start with "J".
'%n'A value that end with "n".
'%oh%'A value that have "oh" in any position.
'_o%'A value that have "o" in the second position.
'J_%'A value that start with "J" and have at least 2 characters.
'J__%'A value that start with "J" and have at least 3 characters.
'J%n'A value that start with "J" and ends with "n".

Example:

Consider a database containing a table called Employee with the following records:

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

  • Using the % Wildcard : To select all records of the Employee table with Name starting with 'Jo', the SQL code is given below.

    SELECT * FROM Employee
    WHERE Name LIKE 'Jo%';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
  • Using the % Wildcard with NOT LIKE operator: NOT LIKE operator is used as the negation of LIKE operator. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:

    SELECT * FROM Employee
    WHERE Name NOT LIKE 'Jo%';
    

    This will produce the result as shown below:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800
  • Using the _ Wildcard : To select all records of the Employee table with Name containing 'o' as second character, the SQL code is mentioned below.

    SELECT * FROM Employee
    WHERE Name LIKE '_o%';
    

    The result of the above code will be:

    EmpIDNameCityAgeSalary
    1JohnLondon253000
    3JoParis272800
  • Using the _ Wildcard with NOT LIKE operator: To select all records of the Employee table with Name not containing 'o' as second character, the SQL code is given below:.

    SELECT * FROM Employee
    WHERE Name NOT LIKE '_o%';
    

    The result of the above code will be:

    EmpIDNameCityAgeSalary
    2MarryNew York242750
    4KimAmsterdam303100
    5RameshNew Delhi283000
    6HuangBeijing282800

❮ SQL - Operators