Salesforce SOQL’s weird syntax for NOT LIKE queries
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.