To reproduce the problem, on a brand new fresh organization I have created two records

insert new List<Account>{
    new Account(Name = 'FDD App'),
    new Account(Name = 'FDD MatureApp')

Now, SOQL query select id, name from Account where name like '%app%' returns both records

enter image description here

while SOSL query FIND {*app*} IN ALL FIELDS RETURNING Account (Id, Name) returns only the first record but doesn't return the second record even if I put asterisks around the search term enter image description here

Why does this happen? Is SOSL Full text search broken in Salesforce and why? Why SOSL fails to find the record with search term in it?

I am not asking about why search clause with term surrounded by asterisks doesn't return records, am I asking how do I find records like "FDD MatureApp" using SOSL. Is this possible to get full text search results to find "FDD MatureApp" record using SOSL by search term "App"?

  • @sfdcfox: so do you mean there is no way to obtain full-text search results using SOSL?
    – Patlatus
    Jun 19, 2018 at 14:43
  • 1
    Basically, you need to know at the least the first character of the word to find it: M*App would return MatureApp (but not App), but *App* results in the leading * being ignored; the index cannot find words in the middle or end of other words. It's not really a "full text" search, it's an indexed search.
    – sfdcfox
    Jun 19, 2018 at 14:45
  • See the wildcards section in this page developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… Jun 19, 2018 at 14:46
  • 2
    @Patlatus I agree. It's a limitation of the search engine. Why they did this, I have no idea, but that's the limitation on searching. You can only wildcard in the middle or end. Programatic's link explicitly states this as well.
    – sfdcfox
    Jun 19, 2018 at 14:47
  • 3
    It's not a bug, it's a feature. It's literally in the documentation. It's been this way since Salesforce's infancy. As a programmer, I hypothesize that it's because they use a binary tree indexing system, so they don't have a way to find bits in the middle without searching the entire tree. The obvious solution here would be to index every possible permutation of the word, which would probably expand the index by ~5x.
    – sfdcfox
    Jun 19, 2018 at 14:51


Browse other questions tagged .