How to query Soql not like?

3.5K Asked by AmandaHawes in Salesforce , Asked on May 24, 2024

the soql in the following code is failing. The error message is: "Compile failure on line 6, column 5: Unexpected token '<'."

Map profileIds = new Map();
profileIds.put('00eee000000xxxx','System Administrator');
profileIds.put('00eee00000yyyyy','YYY Profile');
profileIds.put('00eee00000zzzzz','ZZZ Profile');

List users = [SELECT Id, Name, Email, ProfileId FROM User WHERE NOT(Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet()];
System.debug(users.size());
for (User usr: users)
{
    System.debug(usr.id+'    '+usr.Email);
}

When I run the Query in SOQL Editor, it says "unexpected token: AND"

SELECT Id, Name, Email, ProfileId FROM User WHERE NOT(Email like '%@example.com') AND ProfileId NOT IN ('00eee000000xxxx','00eee00000yyyyy','00eee000000zzzz')

When I remove one of the clauses from the where statement, it works. I do not understand what's wrong with the query. I tried wrapping the query with in parenthesis too, but no luck. for example

 List users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT(Email like '%@example.com') AND ProfileId NOT IN :profileIds.keySet())];
 List users = [SELECT Id, Name, Email, ProfileId FROM User WHERE (NOT(Email like '%@example.com') AND (ProfileId NOT IN :profileIds.keySet()))];

Answered by Al German

Regarding Soql not like -



You have your parentheses wrong.

Instead of:

NOT(Field__c LIKE 'fuzzymatch')

Use:

(NOT Field__c LIKE 'fuzzymatch

Your Answer

Answer (1)

Ranjana

In Salesforce Object Query Language (SOQL), the LIKE operator is used to perform wildcard searches on text fields, similar to SQL. However, SOQL does not directly support a NOT LIKE operator. To achieve the functionality of NOT LIKE, you can use a combination of the NOT operator with LIKE, or you can use the EXCLUDES operator if dealing with multi-select picklists.

Here's how you can construct queries to simulate NOT LIKE in SOQL:

Using NOT with LIKE

SELECT Id, Name FROM Account WHERE Name NOT LIKE 'Test%'

Using NOT with INCLUDES for Multi-Select Picklists

If you are working with multi-select picklist fields, you can use NOT with INCLUDES.

Example: Query for accounts where the multi-select picklist field Industry does not include 'Technology'.

SELECT Id, Name FROM Account WHERE NOT (Industry__c INCLUDES ('Technology'))

Using NOT with Wildcards

SOQL supports the % wildcard to represent zero or more characters and the _ wildcard to represent a single character.

Example: Query for accounts where the Name does not contain 'Corp'.

SELECT Id, Name FROM Account WHERE Name NOT LIKE '%Corp%'

Using != for Exact MatchesIf you need to exclude exact matches, you can use the != operator.

Example: Query for accounts where the Type is not 'Customer'.

SELECT Id, Name FROM Account WHERE Type != 'Customer'

Combining Multiple Conditions

You can combine multiple conditions using AND and OR to create complex queries.

Example: Query for accounts where the Name does not contain 'Corp' and the Type is not 'Customer'.

SELECT Id, Name FROM Account WHERE Name NOT LIKE '%Corp%' AND Type != 'Customer'

Summary

While SOQL does not have a direct NOT LIKE operator, you can achieve the desired results by using NOT with LIKE or other logical operators depending on your specific needs. Here are the key points:

7 Months