I'm aware of this and this posts. They very well explains how to bulkify LIKE queries. But I have to query on multiple fields.

Let say I have in input a list of account names:

1. Salvator Dali
2. Claude Monet

And alredy in SF the capitalized accounts:


but also one particular case that I don't want inside the query result:


How to write a query that, given a list with 1 and 2 gives me the results 3 and 4 but not the 5?



I have to query on Firstname and Lastname on Person Account objects. (in the case 1 the Firstname will be Salvator and Lastname will be Dali) and in input I will have two List: one for firstnames and one for lastnames

  • You don't need fuzzy search here, SOQL is case-insensitive.
    – Adrian Larson
    Jun 4, 2015 at 14:22
  • Ohu.. so, what the query looks like?
    – mcmc
    Jun 4, 2015 at 14:33
  • Cant you do ... "WHERE myField__c LIKE :myListToRetreive AND myField__c NOT LIKE :myListToExclude"
    – James
    Jun 4, 2015 at 14:34

1 Answer 1


You didn't specify which other fields you want to search on, but for your simple use case, the following will work:

Set<String> names = new Set<String> { 'SALVADOR DALI', 'CLAUDE MONET' };
List<Account> matches = [SELECT Name FROM Account WHERE Name IN :names];
//Finds Salvador Dali and Claude Monet
//Ignores Claude Dali


Okay so I understand the problem better now, you have two potential approaches, either you can find all exact matches by merging in clauses like (FirstName='Salvador' AND LastName='Dali') with an OR join, or you can query where the FirstName and LastName are in your collections and do some post-processing. I prefer the latter especially because it does not require any dynamic SOQL.

Collections Approach:

Map<String, String> firstToLast = new Map<String, String>
    'SALVADOR' => 'DALI',
    'CLAUDE' => 'MONET'
List<Account> matches = new List<Account>();
for (Account person : [
    SELECT FirstName, LastName FROM Account
    WHERE FirstName IN :firstToLast.keySet()
    AND LastName IN :firstToLast.values()
    if (firstToLast.get(person.FirstName) == person.LastName)

Dynamic SOQL Approach:

Map<String, String> firstToLast; // same as above
List<String> nameClauses = new List<String>();
for (String firstName : firstToLast.keySet())
    String nameClause = '(FirstName=\'' + firstName + '\'' +
        ' AND LastName = \'' + firstToLast.get(firstName) + '\')'
String namesClause = String.join(nameClauses, ' OR ');
String soql = 'SELECT FirstName, LastName FROM Account WHERE ' + namesClause;
List<Account> matches = Database.query(soql);
  • see the edit please
    – mcmc
    Jun 4, 2015 at 14:40
  • Gotcha. I removed LIKE from your post title because it was very misleading. You do have exact matches, the problem is of a different nature. :)
    – Adrian Larson
    Jun 4, 2015 at 14:42
  • nice, so you are getting all the combinations and with post-processing you're filtering only the right ones.. but.. I was googling about SOSL.. souldn't be better?
    – mcmc
    Jun 4, 2015 at 14:52
  • because on scaling up properly.. in an org with A LOT of accounts.. this for loop can overtake the 10s.. isn't it?
    – mcmc
    Jun 4, 2015 at 14:54
  • Depends mostly on the size of your input collections. If it's smaller, go with the collections approach, if it's larger, maybe move to dynamic soql. You have to worry about character limits though...
    – Adrian Larson
    Jun 4, 2015 at 14:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .