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.

Share this Post

About Matt

Matt is a seasoned Salesforce Developer / Architect, with implementations of Sales Cloud, Service Cloud, CPQ, Experience Cloud, and numerous innovative applications built upon the Force.com platform. He started coding in grade 8 and has won awards ranging from international scholarships to internal corporate leadership awards. He is 37x Certified on the platform, including Platform Developer II, B2B Solution Architect and B2C Solution Architect.