How to Use Like With Lists and Sets In SOQL Queries

Sharing is Caring

It’s no secret, that Salesforce programmers are huge fans of collections like lists, sets, and maps because they allow for much more efficient queries. I’ve decided to put together a post about how to use the Like Operator in SOQl and how to use it with Lists and Sets.

In a regular situation, we would use the LIKE operator because we’re looking for duplicate accounts or contacts or we’re looking for something but don’t necessarily have the exact value. A really good usecase, and where I first found this out, is looking at the result of a call. When Salesforce is initially setup, it’s common to use a text field for things when a picklist should have been used, so for our example we’ll look at to see if the Rep left a voicemail.

Note: it’s also very possible to do this for Opportunities, Accounts or any other object in Salesforce.

So, just looking for voicemail we would do something like this:

List<Task> tsks = [select Id, Call_Result__c from Task Where Call_Result__c LIKE '%voicemail%'];

for (Task tsk : tsks) {
 doSomething(tsk);
}

This works pretty well, assuming that your reps are consistently using voicemail in this text field. What if they’re not? We could update our query to use two different clauses, like so:

List<Task> tsks = [select Id, Call_Result__c from Task Where Call_Result__c LIKE '%voicemail%' OR Call_Result__c LIKE '%VM%'];

for (Task tsk : tsks) {
 doSomething(tsk);
}

Ok, so this works pretty well too but imagine that your sales reps are sometimes spelling voicemail incorrectly and your company hasn’t yet come up with a way to standardize the values yet so you need to add some additional clauses. If you add multiple like clauses it gets confusing or if you suddenly need to add an AND condition it can become really difficult to manage.

Did you know that you can use lists or sets in SOQL to use like in the where clause?


//Possible options we're going to query for.
Set<String> voiceMailOptions = new Set<String>{'%voicemail%', '%voicemali%', '%vm%','%message%'};

List<Task> tsks = [select Id, Call_Result__c from Task Where Call_Result__c LIKE :voiceMailOptions];

for (Task tsk : tsks) {
 doSomething(tsk);
}

Hope this helps!

Sharing is Caring

Brian is a programmer and technology leader living in Niagara Falls with more than ten years of development experience. He is passionate about automation, business process re-engineering, and building a better tomorrow.

Brian is a proud father of four: two boys, and two girls and has been happily married to Crystal for more than ten years. From time to time, Brian may post about his faith, his family, and definitely about technology.