Can LIKE Work with Multiple Values in SOQL Queries?

Question:

Is it possible to use the LIKE operator in SOQL with a list of values in a manner similar to the IN operator? For example:

SELECT Id FROM Account WHERE Name LIKE IN :nameList

Here, nameList would be a list of strings such as ['Acme%', 'Ib%']. While dynamic queries using OR for each element in the list are possible, is there a more elegant way to achieve this?

CRS Info Solutions offers expert-led Salesforce Online Course with real-world projects, certification guidance, and career-focused support.

Answer:

No, it is not possible to directly combine the LIKE operator with the IN operator in SOQL. However, Salesforce provides an elegant solution to achieve the same result using a bind variable with the LIKE operator and a list of filter values. The query below demonstrates this approach:

String[] filters = new String[]{'Acme%', 'Ib%'};
List<Account> accs = [SELECT Id, Name FROM Account WHERE Name LIKE :filters];

The provided code snippet demonstrates how to use the LIKE operator in SOQL with a list of filter values in Salesforce Apex.

Code Breakdown:

String[] filters = new String[]{'Acme%', 'Ib%'};

Here’s an explanation:

• String[] filters: This creates a list (array) of strings in Apex. Each string in the list represents a pattern to match using the LIKE operator.
• 'Acme%' and 'Ib%': These are the patterns for the LIKE operator.
  • 'Acme%': Matches any Name that starts with “Acme”.
  • 'Ib%': Matches any Name that starts with “Ib”.
• The % character is a wildcard in SOQL, matching zero or more characters.

List<Account> accs = [SELECT Id, Name FROM Account WHERE Name LIKE :filters];

Here’s an explanation:

• List<Account> accs: This is a list of Account objects that will store the results of the SOQL query.
• SELECT Id, Name FROM Account: This SOQL query fetches the Id and Name fields from the Account object.
• WHERE Name LIKE :filters: The LIKE operator is used to filter accounts where the Name field matches any of the patterns in the filters list.
  • The colon (:) before filters indicates a bind variable, which allows you to pass the filters list dynamically into the query.

How It Works:

The SOQL query processes the filters list as though it were multiple OR conditions, effectively behaving like:

SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%' OR Name LIKE 'Ib%'

The query executes and retrieves all Account records where the Name starts with “Acme” or “Ib“.The results are stored in the accs list.

Advantages:

• Conciseness: You avoid manually writing multiple OR conditions for each pattern.
• Dynamic Filters: You can dynamically populate the filters list at runtime, making the query more flexible.
• Readability: The code is clean and easy to understand.

This method is efficient, especially when dealing with multiple patterns, and avoids the complexity of constructing dynamic SOQL queries.

In this example, the filters list contains multiple patterns, and SOQL processes each pattern with the LIKE operator as though they were combined with OR. This method is concise and avoids the need to construct dynamic SOQL queries.

If you were to use a dynamic query to achieve the same result, it might look like this:

String[] filters = new String[]{'Acme%', 'Ib%'};
String query = 'SELECT Id, Name FROM Account WHERE ';
List<String> conditions = new List<String>();

for (String filter : filters) {
    conditions.add('Name LIKE \'' + filter + '\'');
}

query += String.join(conditions, ' OR ');
List<Account> accs = Database.query(query);

The provided code demonstrates how to construct a dynamic SOQL query that uses the LIKE operator with multiple values in Salesforce. Here’s a step-by-step explanation of the code:

Defining the filter values:

String[] filters = new String[]{'Acme%', 'Ib%'};

This line defines an array of strings called filters. Each string represents a pattern to be used in the LIKE clause of the SOQL query. For example, 'Acme%' will match any Account with a name starting with “Acme“, and 'Ib%' will match any Account with a name starting with “Ib”.

Initializing the query string

String query = 'SELECT Id, Name FROM Account WHERE ';

This initializes a String variable query with the basic structure of the SOQL query. It specifies that you want to retrieve the Id and Name fields from the Account object, and it starts the WHERE clause.

Building the conditions for the WHERE clause

List<String> conditions = new List<String>();

This creates an empty list called conditions, which will store individual conditions for the WHERE clause (i.e., the parts of the query that use LIKE for each pattern in the filters array).

Adding conditions based on the filters:

for (String filter : filters) {
    conditions.add('Name LIKE \'' + filter + '\'');
}

This for loop iterates over each string in the filters array. For each filter, it constructs a string that represents a condition of the form Name LIKE 'filter'. The conditions list will thus contain conditions such as Name LIKE 'Acme%' and Name LIKE 'Ib%'.

Joining the conditions with OR:

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

After building all the individual conditions, this line concatenates them into a single string, joining each condition with the OR operator. The WHERE clause will now look like:

WHERE Name LIKE 'Acme%' OR Name LIKE 'Ib%'

Executing the dynamic SOQL query:

List<Account> accs = Database.query(query);

Finally, this line executes the dynamically constructed SOQL query using Database.query(). The result is stored in the accs list, which will contain the Account records whose Name matches any of the patterns in the filters array.

This approach works but is less elegant and introduces potential risks, such as SOQL injection, unless the input is thoroughly sanitized.

The first method, using bind variables with LIKE, is simpler, safer, and recommended for use in most scenarios.

Summing Up

In SOQL, the LIKE operator cannot directly be combined with the IN operator to match multiple values at once. However, you can achieve similar functionality by using dynamic queries that combine multiple LIKE conditions with the OR operator. While this approach requires constructing the query manually, it provides a flexible way to search for records matching multiple patterns. A more elegant solution is to bind a list of filter values to the LIKE operator, which simplifies the query and avoids the need for dynamic string concatenation.

The dynamic query approach allows for flexibility but introduces potential risks, such as SOQL injection, unless the inputs are properly sanitized. The recommended approach is to use bind variables with the LIKE operator, as it is simpler, safer, and more efficient. This method allows you to search for multiple patterns in a single, concise query without the complexity of manually building dynamic queries.

Transform Your Career with Salesforce Training in Chennai

Our program offers expert certification guidance, in-depth interview preparation, and comprehensive modules covering Admin, Developer, and AI tracks. Salesforce training in Chennai With detailed class notes and hands-on learning, we equip you with the skills to excel in the industry and stand out in the competitive job market.

Experience the best in Salesforce training—join our free demo class today and start your journey toward success!

Related Links :

• Understanding SOQL
  
• SOQL Query in Salesforce Apex & Examples
  
• String Methods in Lightning Web Components