SOQL/SOSL: Using NOT LIKE

I’ve had to struggle to remember how to do this enough times that I think it’s worth documenting.

Using the LIKE keyword to filter records in a SOQL or SOSL query is a great way to do searching using a partial string match. Here’s an example:

  select Id, Name
  from Account
  where BillingState = 'WA'
    and Name like '%ray%'

But to exclude using the LIKE operator is a little more counter intuitive. Here’s the correct way to do it:

Good

  select Id, Name
  from Account
  where BillingState = 'WA'
    and (not Name like '%ray%')

Note that if you do any similar variants which you think might work, they will fail with the following error:

MALFORMED_QUERY … unexpected token: ‘not’

or

MALFORMED_QUERY … unexpected token: ‘like’

Here are some ways which you’d think might work, but do not.

Bad

  select Id, Name
  from Account
  where BillingState = 'WA'
    and not Name like '%ray%'

Bad

  select Id, Name
  from Account
  where BillingState = 'WA'
    and not (Name like '%ray%')

Bad

  select Id, Name
  from Account
  where BillingState = 'WA'
    and Name not like '%ray%'
This entry was tagged , , . Bookmark the permalink.