I had thought that
% was the "zero or more" wildcard character, but that's not for SOSL (it's for the
LIKE operator, primarily for SOQL). SOSL uses
? in the
FIND clause as the wildcards for "zero or more unknown characters" and "exactly one unknown character" respectively (relevant documentation).
So failing that, I think the explanation here boils down to how Salesforce is tokenizing strings.
There is a help article that goes over how Salesforce tokenizes strings.
In general, a string is split into tokens whenever there's a change in the character class (i.e. alpha, numeric, whitespace).
So the list of tokens for "blue1" should be:
- "blue1" (i.e. the original string)
FIND 'blue1' ought to find a match based on the first token (and the tokens are generated based on the data stored on the SObject). It's much less clear if/how Salesforce tokenizes the search terms in the
FIND clause, but doing some reading up on Apache Solr (which salesforce was using back when I attended Dreamforce around 2015-2016, and may still be using) I would make an educated guess that the search terms are also tokenized.
Salesforce also limits the intermediate result set that it applies filters on (2000 records for general searches, 200 records for things like lookup filters), and trying to match partial tokens may or may not work (you're more likely to need to use a formula field to give you a partial string for Salesforce to tokenize).
So, with all of that in mind
Here's my hypothesis:
- Tokens like single numbers and single letters are likely to return so many results that Salesforce is limiting the intermediate result set
- Your target record happens to be outside of the intermediate result set, so the SOSL query returns nothing (or records other than the one you want) when you
- By including a non-alphanumeric character (and it should work with almost any non-alphanum, non-reserved character, I've tried
;), you're subtly changing how the search term is being tokenized
- Being non-alphanum, salesforce is removing it from the search term tokens
- It's forcing the tokenizer to create tokens in specific locations
- This new list of tokens, through some internal magic, is probably excluding longer tokens like "12"
- Since your new set of tokens isn't as open-ended of a search (i.e. it's looking for "1", not effectively "1*"), the intermediate result set isn't being limited (or if it is, your target record happens to be in the intermediate result set)