How to Use Like With Lists and Sets In SOQL Queries

How to Use Like With Collections in SOQL
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!

Author: Brian Cline

Brian is a programmer living in Niagara Falls with ten years of development experience. He is passionate about automation, business process re-engineering, and gamification. For the last six years or so, he has been working with Salesforce and JavaScript.

Related Posts

Sharing is Caring