SOQL multiple LIKE matches [duplicate]

Question

I am trying to figure out the best way to do multiple LIKE matches in a complicated soql statement.

I want to change the hubFilter value from just "BCS%" to "BCS%,Benefit Advisor,Mgr of Ben,BSR".

I tried this and got nothing back so I assume it's not reading those as separate values. Any ideas?

Here is a basic example of what my query looks like:

list<BCS__c>hubs = new list<BCS__c>();
private string hubfilter;
if(showAll == TRUE){
    hubFilter = '%';
}else{
    hubFilter = 'BCS%';
}
hubs = [
    SELECT /*fields and subqueries*/ FROM BCS__c
    WHERE /*other clauses*/ AND Title__c LIKE :hubFilter
];

Answer 1

You can use a collection with LIKE:

Set<String> fuzzyMatches = new Set<String> { 'A%', 'B%', 'etc%' };
List<MyObject__c> records = [SELECT Id FROM MyObject__c WHERE Field__c LIKE :fuzzyMatches];

You can also define the collection inline:

SELECT Id FROM MyObject__c WHERE Field__c LIKE ('A%', 'B%', 'etc%')

---

Your specific hubfilter would be:

new Set<String> { 'BCS%', 'Benefit Advisor', 'Mgr of Ben', 'BSR' };

Note that you only use a fuzzy match on the first string. It is unclear if any of the other values should also be a fuzzy match.

Answer 2

For anyone else who finds this post in future, I ended up with this code.

set<string> hubfilter = new set<string>();

        if(showAll == TRUE){
            hubfilter = new Set<String> { '%' };
        }else{
            hubFilter = new Set<String> { 'BCS%', 'Benefit Advisor', 'Mgr of Ben', 'BSR' };
        }