SOSL Alphanumeric Search String Behavior, solution using %

Question

I'm trying to understand some SOSL behavior.

I had an issue where I needed to do a SOSL search using alphanumeric search strings. EX: 'blue1' We found that attempts to search using alphanumeric data would fail to return existing results. The solution that I came across, somewhat randomly, was to wrap the the numbers in '%'. Example: 'blue%1' or 'blue%1%', either would work. Modifying the search string in this way would correctly return the result for and only for 'blue1', no partial matching, such as 'blue12'

I'm wondering why its behaving like this. Why do regular alphanumeric search strings fail?
I understand that SOSL is strictly for text, but it can find numbers in text. Example: '12' is able to find 'blue12' What is the purpose of %? I've been able to find nothing on the official website, reference guide, or trailhead.

I'd appreciate any insight into this.

Answer 1

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 * and ? 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)
• "blue"
• "1"

and 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 FIND 'blue1'
• By including a non-alphanumeric character (and it should work with almost any non-alphanum, non-reserved character, I've tried %, # and ;), 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)