8

I have a list of accounts that I would like to perform a LIKE query on. Ideally, my query would be something like:

SELECT id, owner.name FROM Account WHERE name LIKE IN :entityList

Is there any way I can do this?

The issue is that my entity names come from a third party source, which means that small variations in name may be present, i.e

"Bay Ridge apt." VS "Bay Ridge Apartments"

It's hard to predict where the difference in spelling might be, and I was hoping that the LIKE filter might be some magical filter that can figure this out for me and match on a substring (i.e. "Bay Ridge").

The Salesforce guide doesn't include this option in their guide, so it might not be possible to combine a LIKE and an IN. Maybe there's a solution around it?

Maybe there's a way to perform a SOSL query on a list? Something like :

Find {entityList} In Account

I can't seem to find this any where...

Any guidance?

EDIT: It seems that it's not really possible to 'bulkify' SOSL queries. I could have ran a loop, but this would have hit governor limits. Using the LIKE filter with strategic wildcards was key.

  • To Get Fuzzy like you want, I think you would need to do some Natrual Language Processing. I don't think the SF platform is really capable of what you want without investing pretty heavily. You can combine LIKE and IN, though. It's just that LIKE isn't nearly as magical as you want it to be. – Adrian Larson Feb 11 '15 at 0:39
  • I tried combining LIKE and IN, got a parse error (maybe was something else?). Either way, maybe a SOSL query would work? I mean, I can type these names into the salesforce search and get results - but not in SOQL with LIKE. – ZAR Feb 11 '15 at 0:41
  • Right, thats SOQL. How to perform a similar search with SOSL (mind you, it isn't just a FIND WHERE IN, the list has to be in the FIND clause) – ZAR Feb 11 '15 at 0:55
  • Hmm, I am not as familiar with SOSL. If you are tied to that I am not sure how to accomplish what you need. – Adrian Larson Feb 11 '15 at 0:59
6

As mentioned in this post, you can perform LIKE on a list of matches. You might want to split on spaces and create a pool of candidates. It may be worthwhile to consume an extra query to try to find exact matches first. You will also probably get a better candidate pool if you use pair-wise neighbor combinations instead of individual split results.

public static List<Account> findCandidates(String potentialName)
{
    if (String.isBlank(potentialName)) return null;
    List<Account> candidates = [SELECT Id FROM Account WHERE Name = :potentialName];
    if (!candidates.isEmpty()) return candidates;

    List<String> partialMatches = new List<String>();
    for (String fragment : potentialName.split(' '))
    {
        partialMatches.add('%' + fragment + '%');
    }
    return [SELECT Id FROM Account WHERE Name LIKE :partialMatches];
}

To get pairwise neighbors would look something like:

static List<String> getFragments(String potentialName)
{
    if (String.isBlank(potentialName)) return null;

    List<String> fragments = new List<String>();
    List<String> chunks = potentialName.split(' ');

    while (chunks.size() >= 2) // starting with one word should yield an empty result
    {
        String fragment = '';
        if (!fragments.isEmpty()) fragment += '%'; //No leading wildcard
        fragment += chunks.remove(0);
        fragment += chunks.get(0);
        if (chunks.size() > 1) fragment += '%'; // No trailing wildcard
        fragments.add(fragment);
    }

    return fragments;
}
  • I still seem to be having difficulty using both LIKE and IN - I get a parsing error – ZAR Feb 11 '15 at 15:45
  • 1
    You don't use the IN keyword, just use List<String> instead of String. – Adrian Larson Feb 11 '15 at 15:47
  • I presume the two lines between the if statements in the while loop are supposed to read fragment += ... not fragments += ... ? I am a novice and I want to make sure that List doesn't interact with operators in a special way. – Lucas Leblanc Jul 2 '15 at 16:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.