[SalesForce] SOSL and “ends with” wildcard

As per the Developer Guide on SOSL WHERE clause, it is used to filter the search results:

By default, a SOSL query on an object retrieves all rows that are visible to the user. To limit the search, you can filter the search result by specific field values.

Furthermore, the syntax of the SOSL also suggests that the WHERE clause is applied to search results, yielded by the text search:

FIND {search_query} RETURNING sObject(fieldName WHERE fieldExpression)

Consider the following example:

FIND {John} IN NAME FIELDS RETURNING Account(Id, Name WHERE IsImportant__c=true), Contact(Id, FirstName, LastName WHERE IsPrimary__c=true)

In the example above, one would expect the search to be performed on all Accounts and Contacts, and the filter subsequently applied to the resulting record set (all Accounts and Contacts where term 'John' is found in Name fields), thus eventually returning only the records meeting the expression criteria (all Important Accounts and Primary Contacts where term 'John' is found), rather than initially filtering all accounts and contacts to important/primary ones and then doing a text search on those.

---

I think in your case using SOSL rather than SOQL might indeed yield performance gains (digression: are all of the searchable fields textual and are they indexed?). If I understood you correctly, your users will be able to enter search terms freely, meaning your search will check against fuzzy matches, meaning you would have to be using wildcards in SOQL conditions. Furthermore, as you mentioned that users will be able to select fields on which to search against, it means you would end up with multiple conditions in WHERE clause. Consider following remarks from the Best Practices for LDV handbook:

Considerations when using SOQL or SOSL:

• Both SOSL search queries and SOQL WHERE filters can specify text to look for. When a given search can use either language, SOSL is generally faster than SOQL if the search expression uses leading wildcards or a CONTAINS term.

• In some cases, when multiple WHERE filters are being used in SOQL, indexes cannot be used even though the fields in the WHERE clause may be indexed. In this situation, decompose the single query into multiple queries each with one WHERE filter and then combine the results.

Given the problem presented (users being able to select multiple fields and enter search text against each field), I assume you considered translating the search conditions into the WHERE condition in SOSL. Does not sound like a good idea, as you might end up with the following:

...RETURNING YourTargetObject(Id WHERE selectedField1 LIKE %textInput1% OR selectedField2 LIKE %textInput2%)

Apart from the above, the question arises what would you use as the primary search query in such case. Instead of such WHERE filtering, it would seem wiser to have all the user's textual inputs bundled in the SOSL's FIND search term, especially having the following performance consideration from the Force.com SOQL and SOSL Reference in mind:

• SOSL can tokenize multiple terms within a field (for example, multiple words separated by spaces) and builds a search index off this. If you’re searching for a specific distinct term that you know exists within a field, you might find SOSL is faster than SOQL for these searches. For example, you might use SOSL if you were searching for “John” against fields that contained values like “Paul and John Company”.

So you might find it that the latter could be more efficient than the former:

FIND {textInput1* OR textInput2*} RETURNING YourTargetObject

But to give you a definite answer whether SOQL or SOSL would be more efficient is kind of an ungrateful task, as important details are missing (are the searchable fields only searchable by text, how many fields can the search check against, how complex can the custom search logic become etc). Your best bet would be to pull in some typicall searches users might make and compare both SOQL and SOSL performance.

The * character is not a special character as far as SOQL is concerned. Here's a trivial proof for you:

And here's the same in Apex:

System.debug(
    Database.query(
        'SELECT Name FROM Account WHERE Name LIKE \'**Demo\''  
    )
);

Or, adding a wildcard at the end:

System.debug(
    Database.query(
        'SELECT Name FROM Account WHERE Name LIKE \'**Demo%\''  
    )
);

In other words, you're likely over-sanitizing your query.

The code that should work correctly in your org would be:

System.debug(
  Database.query(
    'Select Id, Name from Account where Name Like \'*%\''
  )
);