How to use LIKE and IN together in SOQL?

Question

I have a requirement, for each Accountwhose name contains ‘Sale’, ‘Opportunity’, or ‘Catalog’, set Price_Type__c to ‘Net’.

This is what I code in Anonymous Apex:

set<string> myString = new Set<String>{'%Sale%', '%Opportunity%', '%Catalog%'};
List<Pricebook2> myList = [select Name from Pricebook2 where name LIKE IN: myString];
for (Pricebook2 rec : myList) {
    rec.Is_Auto_Maintained__c  = true;
    rec.Price_Type__c ='Net;'
}
update myList;

and it threw an error : "

expecting a colon, found 'IN'

". So is there any other solution to this?

score 26 · Answer 1 · answered May 28 '14 at 10:58

26

Somehow I found the solution :

String[] nameFilters = new String[]{'%Sale%', '%Opportunity%', '%Catalog%'};
List<Pricebook2> myList =
    [select Name
     from Pricebook2
     where name LIKE :nameFilters 
    ];
for (Pricebook2 rec : myList) {
        rec.Is_Auto_Maintained__c  = true;
        rec.Price_Type__c ='Net';
}
update myList;

answered May 28 '14 at 10:58

Noob_NoVoice

2,709
16
66
88

Is this working fine in your requirement? – highfive May 28 '14 at 11:15
@highfive : Yes, it is working. I checked the SObject data. – Noob_NoVoice May 28 '14 at 11:29
1

Wow....the more you know.... I never knew you could do this. Great to know. Found this other answer on here that confirms as well. Learn something new everyday. http://salesforce.stackexchange.com/questions/12522/is-there-a-way-to-do-like-against-a-list-of-values – Chris Duncombe May 28 '14 at 11:32
You incorrectly added the IN operator in the original. IN isn't needed when passing the set/list to the LIKE operator. – drakored Nov 27 '14 at 12:43
brilliant answer! – sfdcFanBoy Jan 27 '16 at 11:55

score 4 · Answer 2 · answered May 28 '14 at 10:56

4

You could solve this just by using some Or's.

string s1 = '%Sale%';
string s2 = '%Opportunity%';
string s3 = '%Catalog%';
List<Pricebook2> myList =
    [select Name
     from Pricebook2
     where Name LIKE : s1 Or Name Like : s2 Or Name Like : s3];

answered May 28 '14 at 10:56

Chris Duncombe

24,176
11
75
116

Yes, this is the only way that I'm also can think of. But if your string set is large this will be a pain – highfive May 28 '14 at 10:57
This will work, but looks like you figured it out on your own, thanks for sharing... – Chris Duncombe May 28 '14 at 11:33

How to use LIKE and IN together in SOQL?

2 Answers2

Linked