How to query Soql not like?

3.8K 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

Answers (2)

Ranjana

In Salesforce’s SOQL (Salesforce Object Query Language), the NOT LIKE operator can be used to filter records where a field does not match a specific pattern. This is particularly useful when you want to exclude records based on partial matches. Here’s how you can use it:

Syntax of NOT LIKE in SOQL:

  SELECT  FROM  WHERE  NOT LIKE ''

Key Points:

The NOT LIKE operator works with wildcards:
% matches zero or more characters.
_ matches a single character.
Use single quotes (') around the pattern.
NOT LIKE is case-insensitive.

Examples:

Exclude Records with a Specific Pattern:

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

This query retrieves all accounts where the Name does not start with "Test".

Exclude Records Containing Certain Words:

  SELECT Email FROM Contact WHERE Email NOT LIKE '%@example.com'

This fetches contacts whose email addresses do not belong to the "example.com" domain.

Combine with Other Filters:

  SELECT Name FROM Account WHERE Name NOT LIKE 'Demo%' AND Industry = 'Technology'

This retrieves technology accounts where the name does not start with "Demo".

Best Practices:

Ensure proper use of wildcards to avoid unintended exclusions.
Combine NOT LIKE with LIMIT or ORDER BY for better performance when querying large datasets.
Use LIKE or NOT LIKE only on text-based fields such as String, Email, or Picklist.

Using NOT LIKE helps fine-tune your queries, allowing precise data filtering in Salesforce!

3 Weeks

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:

9 Months