Effective use of LIKE keyword in SOQL query

Get the dynamic results based upon the keyword entered by user. LIKE operator in SOQL can query all the results which is having the keyword entered by user.

Posted by Vinay Vernekar 25th April 2020

Applications never be static, they needs to have dynamic functionality. Getting the input from end user, retrieving the data from database, processing on it and then showing the result on user scree. This is what expected from an application. In Salesforce, SOQL language is used to retrieve the data from database. You may face a scenario to retrieve the object records if a particular field value is having ‘XYZ‘ as text. This is where the LIKE keyword is used in SOQL. In this topic, we will try to cover almost all possible scenarios with LIKE keyword.

Scenarios with SOQL LIKE operator

Find the list of account which is having word “UC” in its name.

This is the pretty straightforward scenario we can say, to find such accounts, we need to write SOQL query as follows:

List<Account> Accounts = [SELECT Id, Name from Account where Name like '%UC%']

Lets move towards some advanced scenarios.

Find the list of account which is having the word entered by user on form

This is something we can call dynamic query, because we need to find the accounts based on the word entered by user on UI.

Lets say you have declared and using the apex string variable “SearchKeyword” on visualforce or lightning component. When user enters the keyword and click on submit button, this variable will be holding the value of keyword.

To query all such accounts which is having the keyword in its name, we ofcourse need to user like query. But does the following query will work?

List<Account> Accounts = [SELECT Id, Name from Account where Name like: SearchKeyword]

No its not, you have to append “%” from both sides. To do that, following needs to be done in apex:

SearchKeyword = '%'+SearchKeyword+'%';
List<Account> Accounts = [SELECT Id, Name from Account where Name like : SearchKeyword];

Find the list of account in which name contains either of the words

Suppose we have a list of search keywords in apex as follow:

List<String> Keywords = new List<String>{'Keyword 1', 'Keyword 2', 'Keyword 3'};

Now to find the list of accounts in which name contains any of the text present in the above list, we need to write SOQL query as:

List<Account> Accounts = [SELECT Id, Name from Account where Name like : Keywords ];

Query will automatically compile even if its a single string or the list of string.

We have seen scenarios which gives results of having a keyword, but what if we need the results which do not have the keywords. In this case with need to use NOT along with LIKE operator in SOQL.

Find the list of account which is NOT having word “UC” in its name.

Its the basic query and will look like:

List<Account> Accounts = [SELECT Id, Name from Account where (NOT Name like '%UC')

Find the list of account where name do not contain word entered by user

The query will become:

SearchKeyword = '%'+SearchKeyword+'%';
List<Account> Accounts = [SELECT Id, Name from Account where (NOT Name like : SearchKeyword)];

Placement of “%” character also plays important role to give you the results based upon the need. There are following scenarios with % character

  • Starting with text ‘ABC’ : ‘ABC%‘
  • Ending with text ‘ABC’ : ‘%ABC’
  • Contains text ‘ABC’ : ‘%ABC%‘
  • Starting with ‘ABC’ and Ending with ‘PQR’ : ‘ABC%PQR’