SOQL 'LIKE' and wildcards '%' not returning full results

Question

I'd like to get a deeper understanding into the SOQL use of wildcards and the term 'Like'. I have the following query:

 SELECT name, city, state
 FROM Account
 WHERE name LIKE '%'+:searchVar+'%'

Let us say my target account is called 'Salesforce StackExchange LLC'. Inserting this full string as my search yields the one correct result, 'Salesforce StackExchange LLC'. Good.

Then let's cut off some of the string and search for 'Salesforce StackExchange'. Now we get multiple results, one of which is our target. So far, all good.

Finally, if I cut off even more, say "Salesforce", I get a bunch of results, but my target is NO LONGER in the results. No good.

I would expect the behavior of the query to keep increasing the amount of results, but still retain all query results from prior searches. If a result comes up for 'Salesforce StackExchange', shouldn't it also come up for 'Salesforce'?

Reading the SOQL guide seems to confirm my above expectation:

Expression is true if the value in the specified fieldName matches the characters of the text string in the specified value. The LIKE operator in SOQL and SOSL is similar LIKE Like to the LIKE operator in SQL; it provides a mechanism for matching partial text strings and includes support for wildcards.

• The % and _ wildcards are supported for the LIKE operator.

• The % wildcard matches zero or more characters.

• The _ wildcard matches exactly one character.

• The text string in the specified value must be enclosed in single quotes.

• The LIKE operator is supported for string fields only.

• The LIKE operator performs a case-insensitive match, unlike the case-sensitive matching in SQL.

• The LIKE operator in SOQL and SOSL supports escaping of special characters % or _.

So what's going on?

Thanks!

EDIT: actual SOQL code is below (in JS):

var searchQuery = $j('#searchQuery').val() || ' ';
var queryOptions = {
        where: {
            name: {like: '%'+searchQuery+'%'}
        }
    };

    globals.destObjModel.retrieve(queryOptions, function(err, records, event){...}

Answer 1

You may be getting into an issue of selectivity and/or "cost". Your query may not be "selective" enough when you reduce the character string to just "Salesforce". Queries on text fields have the highest "cost" of almost any kind of query you can run on a Salesforce database when you use any of the following: "LIKE", Leading Wildcards (%string), Comparators >, <, =, <=, >=, "NOT LIKE", and "EXCLUDES".

If you go to the Query Optimizer and or the Execution Time Lime in the Developer Console, you'll see what I'm referring to. These force the Query Engine to go through every record to get a result. In essence, as the size of your database increases, you wind up with a non-selective query. Other than Name, none of the fields in your query are indexed, greatly reducing it's selectivity and increasing the cost.

I'll add that one thing which may also be causing your results to decrease is that you're not using a %+ string +% which tells the engine to look for additional words beyond Salesforce, but instead more along the lines of "Salesforces". Your other queries already have a blank space in them while the one for Salesforce does not. So that one doesn't think it should look for non-alphanumeric characters. In any case, your query is a difficult one for the engine to run.

Answer 2

I think you have an error in your SOQL. It should looks like the following in Apex:

Dynamic SOQL:

String query = 'SELECT Id, Name FROM Account WHERE name LIKE \'%' + searchVar + '%\'';
List<Account> accts = Database.query(query);

or using Apex Variables:

List<Account> accts = [SELECT Id, Name FROM Account WHERE name LIKE :('%' + searchName + '%')];

Answer 3

Please try to encodes to the querystring. it encodes the following characters: , / ? : @ & = + $ #

SOQL:

query = "SELECT Id from Account Where Id= '" + id + "' and name LIKE '%"+name+"%' ORDER BY Id DESC";

API URL = instance_url + "/services/data/v20.0/query?q=" +encodeURIComponent(query);