Using not like

Use not like to find strings that do not match a particular pattern.

These two queries are equivalent: they find all the phone numbers in the authors table that do not begin with the 415 area code.

select phone 
from authors 
where phone not like "415%" 
 
select phone 
from authors 
where not phone like "415%"

For example, this query finds the system tables in a database whose names begin with “sys”:

select name 
from sysobjects 
where name like "sys%"

To see all the objects that are not system tables, use:

 not like "sys%"

If you have a total of 32 objects and like finds 13 names that match the pattern, not like then finds the 19 objects that do not match the pattern.

not like and the negative wildcard character [^] may give different results. You cannot always duplicate not like patterns with like and ^. This is because not like finds the items that do not match the entire like pattern, but like with negative wildcard characters is evaluated one character at a time.

A pattern such as like “[^s][^y][^s]%" may not produce the same results. Instead of 19, you might get only 14, with all the names that begin with “s”, or have “y” as the second letter, or have “s” as the third letter eliminated from the results, as well as the system table names. This is because match strings with negative wildcard characters are evaluated in steps, one character at a time. If the match fails at any point in the evaluation, it is eliminated.