2

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

  • I recommend changing the reflected answer to be cognisant of SOQL injection vectors, to be a bit smarter about handling the whitespace (not just spaces, but tabs etc.) and to avoid messing about with remembering to remove stuff you added to the string when you can avoid adding it in the first place rather easily. See my alternative answer. – Phil W Oct 1 '18 at 21:01
0

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.
2

Split the string provided by spaces, then put wildcards between each part of the search query.

If a user provides this text:

Blue Auth

Split it into this:

('Blue, 'Auth')

Then include it in your query like this:

String searchString = 'Blue Label'; // user provided  

List<String> chunks = searchString.split(' ');

List<String> fields = new List<String>{ 'Name', 'Order_Information__c' };

String queryString = 'SELECT Id FROM Order__c WHERE ('; 

for (String field:fields) {
    queryString += ' ' + field + ' LIKE \'%';

    for (String chunk:chunks) {
        queryString += chunk + '%';
    }

    queryString += '\' OR'; 
}

queryString = queryString.removeEnd('OR'); 

queryString += ') LIMIT 50';

System.debug(queryString);

Your end query should look like this:

SELECT Id FROM Order__c WHERE ( Name LIKE '%Blue%Label%' OR Order_Information__c LIKE '%Blue%Label%' ) LIMIT 50
  • I have tried your answer, but now when a user inputs anything in the searchstring it does not populate anything in the vf page – j.koh Oct 1 '18 at 20:18
  • j.koh, this answer contains issues such as exposure to SOQL injections. See my subsequent answer. – Phil W Oct 1 '18 at 20:56

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.