7

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?

| improve this question | |
10

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.

| improve this answer | |
  • 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. – Sergej Utko Sep 27 '13 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 Sep 27 '13 at 13:27

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.