8

I am trying to implement a SOSL search function that uses an "ends with" wildcard, like *searchString in SOQL. No luck! I know about this limitation of SOSL

Asterisks match zero or more characters at the middle or end (not the beginning) of your search term.

So if i search for the "Home" and enters just "ome" i will get no results returned.

Is there are some workarounds for this?

1 Answer 1

11

You can perform a normal SOQL query. The reason why you can't wildcard at the beginning is because the index, like in all database systems, indexes from the beginning of each word. In the case where you have a word like "Home", the SOSL index would contain an entry like:

Term Matching Ids Home Record1, Record5, Record27, ...

Thus, it can match Ho*, or Home*, or Home, but not *ome. This restriction exists because (1) it would be too expensive to index every permutation (e.g. it would have to index home, ome, me, and e, instead of just home), and (2) without a partial index described in 1, a frontal wildcard would cause a full table scan of the index, which is impractical computationally.

Instead, you'd have to just perform a normal SOQL query for each table on each field where you want to find the "ends with" term.

2
  • Thank you for the great answer! Ok, i will try SOQL instead :) just wondering whether SOQL will also work as quickly as SOSL for the 1 million entries. Commented Sep 27, 2013 at 6:32
  • 2
    You'll have to come up with a way to filter on an additional field, or you'll run into a non-selective query error. You may want to look at the documentation, and watch the video on Inside the Query Optimizer
    – sfdcfox
    Commented Sep 27, 2013 at 13:27

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .