SOQL: Bulk Fuzzy Searching

Question

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.

Answer 1

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