+ Start a Discussion
clouddev@surashriclouddev@surashri 

SOQL LIKE using Variable

Hi All,

I have list of email domain example gmail.com, hotmail.com etc which are stored in a List variable. I want to use it in a SOQL LIKE so that I should be able to find all contacts having email ids with these domains. As there are more than 100+ domains I cannot use single query for each domain as it will go over SOQL 100 limit and throw an error

This is my code -

List<String> leadsEmail = new List<String> ();

for(lead l : uniq_leads)
{
            leadsEmail.add('\'%'+l.email.substring(l.email.indexof('@')+1)+'\'');
}

system.debug('Contacts - ' + [Select Id, Email, FirstName, LastName, OwnerId from Contact WHERE Email LIKE :leadsEmail]);


But is not returning any records. Could you please let me know exact syntax to use ? Please reply.

Thanks,

clouddev@Surashri
Best Answer chosen by clouddev@surashri
Swati GSwati G
you can remove escape single quotes while creating leadsEmail list.

Try below code:

List<String> leadsEmail = new List<String> ();

for(lead l : uniq_leads)
{
            leadsEmail.add('%'+l.email.substring(l.email.indexof('@')+1));
}

system.debug('Contacts - ' + [Select Id, Email, FirstName, LastName, OwnerId from Contact WHERE Email LIKE :leadsEmail]);

All Answers

VPROKVPROK
you can try using SOSL, it is faster and there is no limit on number of querries
Swati GSwati G
you can remove escape single quotes while creating leadsEmail list.

Try below code:

List<String> leadsEmail = new List<String> ();

for(lead l : uniq_leads)
{
            leadsEmail.add('%'+l.email.substring(l.email.indexof('@')+1));
}

system.debug('Contacts - ' + [Select Id, Email, FirstName, LastName, OwnerId from Contact WHERE Email LIKE :leadsEmail]);
This was selected as the best answer