NOT LIKE query in SOQL

Posted on 18-01-2013 08:51 by graham
SOQL's syntax differs slightly from SQL, so you might be surprised that some of your expressions don't work. So it is with NOT LIKE condition. In standard SQL you may use the following syntax:
SELECT field FROM tablename WHERE field NOT LIKE '%pattern%'

Correct NOT LIKE syntax for SOQL

However, in Salesforce SOQL the above will not work. To do the analogous query in SOQL, you need to do:
SELECT field FROM tablename WHERE NOT (field LIKE '%pattern%')

NOT LIKE condition combined with other conditions

There are more problems waiting for you if you try to combine the NOT LIKe condition in SOQL with other conditions. For example, the following query:
SELECT field FROM tablename WHERE NOT (field LIKE '%pattern%') AND field2 = 'abc'

will throw the following exception:
MALFORMED_QUERY: ... Unexpected token: AND

It turns out that for the NOT LIKE condition to be combined with other conditions, it has to be enclosed in brackets. The following works fine:
SELECT field FROM tablename WHERE (NOT (field LIKE '%pattern%')) AND field2 = 'abc'


Add comment

Has this tutorial been helpful to you? Or do you see anything wrong? We appreciate your opinion!
Your comment:
Show formatting hints
HTML is disallowed, but in your text you can use the following markup
  • [code][/code] for a block of code
  • [tt][/tt] for inline code
  • [link]link href|link anchor[/link] for links
  • [b][/b] for bold text
+ Ask a question
If you have a technical question related to programming and computers, ask it here. Other users will help you solve it!
Unanswered questions
Share your knowledge by helping others solve their problems