3

I have a request for fix a typo in a long text area. So it might be worthwhile for me to search for the same typo for all the objects of that type. Since SOQL can't filter on long text area, I guess my best bet is SOSL. But SOSL doesn't support wildcards at the beginning. So is there a way I can search through SOSL whether the long text area contains a particular string?

6

You cannot search for wildcards at the beginning of a word, but this doesn't mean you can't search for any word anywhere within a long text area. Let's use an example string with some search terms:

"The quick brown fox jumps over the lazy dog," said I.

First, we need at least two characters, so we can't do this:

FIND {I} ...

Also, we can't search for "stop-gap" words, words so common that they drown out any meaningful results:

FIND {THE} ...

We also can't search for *own to find the word brown:

FIND {*OWN} ...

However, we can find brown by searching for the entire word:

FIND {BROWN} ...

And also by just the first part of the word:

FIND {BRO*} ...

Note that brown isn't the first word; we've matched a result using a word some distance into the string. The only requirement is that we need the first part of the word in order to make a match.

Assuming we also know the first and last letter, we can also search that way as well:

FIND {B*N} ...

This would match brown, brawn, brain, baton, bin, bun, ban, but not bat, Botox, fountain, etc.

Also note that SOSL uses stemming, so you can search for a given word and it will also return matches for plurals, etc. Searching for "run" includes "running" and "ran", for example.

Even in the worse case scenario where you absolutely can't guarantee that the first letter will be a given letter, it would be a simple process to iterate through your language's letters and perform dynamic queries to locate the typo, unless you have to search through a language that has many possible letters, such as Chinese, although I admit this would probably be rare.

As long as you can reasonably assure yourself that the first letter is the correct position and at least one other letter is a guaranteed position, it should be possible to leverage the index to find the results you are looking for.

If none of this answer has been useful so far, you might consider the forceful method: batch apex code. That might look like this:

global class SearchText implements Database.Batchable<myobject__c>, Database.Stateful {
    set<id> results;
    string searchPattern;

    global SearchText(string p) {
        searchpattern = p;
        results = new set<id>();
    }

    global Database.QueryLocator start(Database.BatchableContext bc) {
        return Database.getQueryLocator('select id, myfield__c from myobject__c');
    }

    global void execute(database.batchablecontext bc, myobject__c[] scope) {
        pattern p = pattern.compile(searchPattern);
        for(myobject__c record: scope) {
            if(record.myfield__c != null && p.matches(record.myfield__c)) {
                results.add(record.id);
            }
        }
    }

    global void finish(database.batchablecontext bc) {
        // Email the results to yourself
    }
}

I'd recommend reading the Pattern class for details on matching, and the Messaging class for how to send an email.

  • Thank you so much for such a detailed response. Have learnt a lot. – Lance Shi Jun 3 '14 at 5:45

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.