Salesforce SOQL’s weird syntax for NOT LIKE queries

Matt/ January 18, 2017/ Salesforce, Technology

I just encountered the first SOQL syntax error I’ve had to look up in a long time.  Do you know how to write a NOT LIKE condition in a query?

I’m debugging an issue, and wanted to view a user report in our Salesforce environment.  The query was straightforward enough and I was baffled why I couldn’t get it to work.  I ran:

SELECT 
   city__c, 
   count(id) 
FROM User 
WHERE 
   IsActive = true AND
   FederationIdentifier NOT LIKE 'CQ%'
GROUP BY city__c

But was greeted with the message “Unknown error parsing query.”

I can usually see SOQL syntax issues right away and fix it up but this one I had to Google.  The proper syntax is:

SELECT 
   city__c, 
   count(id) 
FROM User 
WHERE 
   IsActive = true AND
   (NOT FederationIdentifier LIKE 'CQ%')
GROUP BY city__c

Note the necessary brackets, and the syntax: “NOT column_name LIKE value”.

At lunch I asked a colleague and he didn’t know this syntax either – which makes me curious why we haven’t needed to write more NOT LIKE queries than just this one.