[SalesForce] SOQL search with LIKE

I’m very confused with the soql Like operator.

If I have a record with a text field that may contain: "Orders to be shipped, pack blue labels/zip/ authorization slip needed.”
And a user enters “blue authorization” into our search,It will not bring in that order at all.
Here is the query:

string search = 'SELECT id, Name, order_information__c from order WHERE (Name like \'%' + searchstring + '%\' OR order_information__c    like \'%' + searchstring + '%\') Limit 50';
       Orders = Database.query(search);

Basically in our vf page we have a search that a user enters in whatever and it brings in orders that match in either name or order_information__c

If the user types in "blue label", it will bring in records but not "blue authorization".
How can we search the whole string if the search term may contain any of the words in the string?

Salesforce’s standard search is able to do this, that if a user just types in “blue authorization” it brings in any record with that in their order_information

Best Answer

The answer from battery.cord needs some improvements, but gets you in the right direction.

I would suggest something more like:

String search = 'Blue Label';
List<String> fields = new List<String> { 'Name', 'Order_Information__c' };
List<String> fieldClause = new List<String>();
String cleanedSearch = '%' + search.trim().replaceAll('\\s+', '%') + '%';

String query = 'SELECT Id FROM Order__c WHERE ';

for (String field : fields) {
    fieldClause.add(field + ' LIKE :cleanedSearch');
}

query += String.join(fieldClause, ' OR ');

System.debug(query);

The important points here:

  1. You replace all whitespace with % characters, both around and within the search value
  2. You leverage a binding variable for the search value, which avoids SOQL injection issues
  3. You need to actually execute the query in the same method, since this is where the binding variable, 'cleanedSearch', is declared - so replace "System.debug(query)" with your "Database.query(query)" statement.
Related Topic