Tags

,

There may be instances where we need to use NOT LIKE in SOQL(I got to use it recently in my project) and using it in SOQL is little more counter intuitive unlike LIKE.

For instance, we want to fetch Id from Job where Status=Complete,Type contains ‘Physiotherapy’ and it should not contain ‘Pain Management’, for instance Type=’Physiotherapy Pain Management’ should not be fetched and the correct query is as follows:

select id from Job__c where Job_Status__c=’Complete’ and Type__c LIKE ‘Physiotherapy%’ and (NOT Type__c LIKE ‘%Pain Management%’)

You might get MALFORMED_QUERY … unexpected token: ‘not’ or MALFORMED_QUERY … unexpected token: ‘like’, if you try any similar variants as below which would not work:

select id from Job__c where Job_Status__c=’Complete’ and Type__c LIKE ‘Physiotherapy%’ and NOT Type__c LIKE ‘%Pain Management%’

select id from Job__c where Job_Status__c=’Complete’ and Type__c LIKE ‘Physiotherapy%’ and NOT (Type__c LIKE ‘%Pain Management%’)

select id from Job__c where Job_Status__c=’Complete’ and Type__c LIKE ‘Physiotherapy%’ and Type__c NOT LIKE ‘%Pain Management%’

Happy sharing!

Advertisements