How to query NOT LIKE?

the soql in the following code is failing.
The error message is: “Compile failure on line 6, column 5: Unexpected token ‘<‘.”

Map<Id, String> profileIds = new Map<Id, String>();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE NOT(Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet()];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}

When I run the Query in SOQL Editor, it says “unexpected token: AND”

SELECT Id, Name, Email, ProfileId FROM User WHERE NOT(Email like '%@example.com') AND  ProfileId NOT IN ('00eee000000xxxx','00eee00000yyyyy','00eee000000zzzz')

When I remove one of the clauses from the where statement, it works. I do not understand whats wrong with the query. I tried wrapping the query with in parenthesis too, but no luck. for example

 List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT(Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet())];
 List<User> users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT(Email like '%@example.com') AND (ProfileId NOT IN :profileIds.keySet()))];

Answer

You have your parentheses wrong.

Instead of:

NOT(Field__c LIKE 'fuzzymatch')

Use:

(NOT Field__c LIKE 'fuzzymatch')

Attribution
Source : Link , Question Author : Dev Arya , Answer Author : Adrian Larson

Leave a Comment