How to use LIKE and IN together in SOQL?

  • 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?

  • 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;
    

    Is this working fine in your requirement?

    @highfive : Yes, it is working. I checked the SObject data.

    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

    You incorrectly added the IN operator in the original. IN isn't needed when passing the set/list to the LIKE operator.

    brilliant answer!

  • 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];
    

    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

    This will work, but looks like you figured it out on your own, thanks for sharing...

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM

Tags used