Is there way to include and exclude characters in same field in SOQL query?

Question

I have a scenario where I need to query all the records which do not have field value contains Customer , but need to include Customer System and other records.

• Name | API Source| Email

• Record 1 | Customer System|[email protected]

• Record 2 | Customer|[email protected]

• Record 3 | From Web|[email protected]

• Record 4 | System A|[email protected]

In the above example I need to exclude the record 2 and 4 as they do not match my criteria, but need to query all other records. I have tried something like this:

Set<String> excludeSource = new Set<String>{'%Customer%'};
Set<String> inlcudeSource = new Set<String>{'Customer System'};

List<lead> lst Leads = [
    SELECT Id, Name, Status 
    FROM lead 
    WHERE  email = '[email protected]' AND (
        (NOT APISource__c LIKE : excludeAPISource) 
        OR APISource__c IN :includeAPISource
    )
]

but it does not work as exclude condition excludes all the records.is the a way to handle this scenario in single SOQL query?

Answer 1

Executed the following in the dev console.

The following works just fine for me. It returns records A and C (1 and 3)

Set<String> excludeAPISource = new Set<String>{'%Customer%'};
Set<String> includeAPISource = new Set<String>{'Customer System'};

List<lead> lstLeads = [
    SELECT Id, Name, Status, email, APISource__c 
    FROM lead 
    WHERE  email = '[email protected]' AND (
        (NOT APISource__c LIKE : excludeAPISource) 
        OR APISource__c IN :includeAPISource
    )
];
system.debug(lstLeads);

I set up 4 leads in my dev org with the criteria you outlined and the output was as expected:

14:06:38:028 USER_DEBUG [12]|DEBUG|(Lead:{Id=00Q3600000eRDUdEAO, Name=Record C, Status=New - Not Contacted, [email protected], APISource__c=From Web}, Lead:{Id=00Q3600000eRDTzEAO, Name=Record A, Status=New - Not Contacted, [email protected], APISource__c=Customer System})

Answer 2

Just filter on the literal value in this case:

WHERE Email = '...' AND APISource__c = 'Customer System'

Answer 3

basically it should work. have you tried to move the NOT in front of the bracket like

... 
AND (
    NOT(...)
    OR ...
)

not operators have often a very high priority.

Answer 4

Name     | API Source     | Email
-------------------------------------------
Record 1 | Customer System|[email protected]
Record 2 | Customer       |[email protected]
Record 3 | From Web       |[email protected]
Record 4 | System A       |[email protected]

So based on your question you want to exclude where API source is Customer and System A (and I assume anything called System*), but want to include where API source is Customer System or Web (and in fact everything else).

Your query should look like this:

SELECT Id, Name, Status 
FROM Lead 
WHERE  
    email = '[email protected]' 
    AND NOT APISource__c = 'Customer' 
    AND NOT APISource__c LIKE 'System%'

Answer 5

you can try Like operator of SOQL.

LIKE '%Customer System%'