SOQL LIKE with Wildcards: In-Between Word Matching

In Salesforce development, efficiently querying data is paramount. While SOQL offers robust filtering capabilities, sometimes you need to find records where a specific piece of text appears between two other known words. The standard LIKE operator, when combined with the wildcard character %, provides a powerful, albeit sometimes nuanced, way to achieve this. This guide will explore how to effectively use SOQL LIKE with wildcards for in-between word matching, enhancing your Apex query logic.

Understanding SOQL LIKE and Wildcards

The LIKE operator in SOQL is used for pattern matching in string fields. It's most commonly used with wildcards to represent one or more characters. The two primary wildcards available are:

• % (Percent Sign): Represents zero or more characters. This is the workhorse for flexible matching.
• _ (Underscore): Represents exactly one character. While useful, it's less common for in-between word matching.

When we talk about 'in-between word matching,' we're typically looking for scenarios where you know a starting word and an ending word, and you want to find records where some text exists between them. For instance, you might want to find Account descriptions that contain "premium" followed by "customer" with any characters (or no characters) in between.

Let's consider a simple example. Suppose we have a custom Product__c object with a Description__c field, and we want to find products that mention "limited edition" somewhere within their description.

Basic LIKE Usage:

SELECT Id, Name, Description__c
FROM Product__c
WHERE Description__c LIKE '%limited edition%';

This query finds any Description__c that contains the substring "limited edition" anywhere. However, what if we want to be more precise? What if "limited edition" must appear between two other specific terms, like finding descriptions that mention "available" followed by "now" with something in between?

Matching Text Between Two Known Words

To find text between two specific words using SOQL LIKE and wildcards, you'll chain the % wildcard. The pattern generally looks like this:

'WordA%WordB'

This pattern tells SOQL to find records where WordA appears, followed by zero or more characters (%), and then WordB appears. The % will consume any characters, including spaces, punctuation, and other words, that lie between WordA and WordB.

Let's use our Account object as an example. Suppose we want to find Account Industry values that mention "Technology" followed by "Services," with anything in between.

SELECT Id, Name, Industry
FROM Account
WHERE Industry LIKE '%Technology%Services%';

This query will return accounts where the Industry field contains the sequence: the word "Technology", followed by any characters (which could be spaces, other words like "and", "or", punctuation, etc.), followed by the word "Services".

Example Scenarios:

• 'Technology Services' - Matches
• 'Technology and Services' - Matches
• 'Technology, Consulting Services' - Matches
• 'Techology Services' - Does NOT match (typo in "Technology")
• 'Services in Technology' - Does NOT match (order is reversed)

Handling Case Sensitivity

It's important to remember that SOQL LIKE comparisons are generally case-insensitive for standard text fields. So, '%Technology%Services%' will match "technology services", "TECHNOLOGY SERVICES", and mixed-case variations.

Practical Apex Implementation

In Apex, you'll often construct SOQL queries dynamically. This is where LIKE with wildcards becomes incredibly useful. You can build query strings based on user input or other programmatic conditions.

Consider a scenario where you need to search for Contact records based on a partial phrase found in their MailingAddress field. Let's say we want to find contacts whose mailing address contains "Street" followed by "Avenue" with any characters in between.

public class ContactSearchService {

    public static List<Contact> findContactsByAddressPattern(String word1, String word2) {
        if (String.isBlank(word1) || String.isBlank(word2)) {
            // Handle invalid input, perhaps return empty list or throw exception
            return new List<Contact>();
        }

        // Construct the dynamic SOQL query
        String query = 'SELECT Id, Name, MailingAddress FROM Contact WHERE MailingAddress LIKE :searchPattern';

        // Build the search pattern dynamically
        // Note: String.format() is generally for string interpolation, not complex LIKE patterns.
        // We construct the LIKE pattern directly.
        String searchPattern = '%' + word1 + '%' + word2 + '%';

        System.debug('Executing SOQL query with pattern: ' + searchPattern);

        try {
            List<Contact> contacts = Database.query(query, searchPattern);
            return contacts;
        } catch (QueryException e) {
            System.debug('Error executing SOQL query: ' + e.getMessage());
            // Handle the exception appropriately, e.g., re-throw or return empty list
            return new List<Contact>();
        }
    }

    // Example of how to call the method
    public static void performSearch() {
        // Find contacts with 'Street' followed by 'Avenue' in MailingAddress
        List<Contact> results = findContactsByAddressPattern('Street', 'Avenue');

        if (!results.isEmpty()) {
            System.debug('Found Contacts:');
            for (Contact con : results) {
                System.debug('  - ' + con.Name + ', MailingAddress: ' + con.MailingAddress);
            }
        } else {
            System.debug('No contacts found matching the pattern.');
        }
    }
}

In this Apex example:

1. We define a method findContactsByAddressPattern that accepts two string arguments, word1 and word2, representing the words we want to find in sequence.
2. We construct the searchPattern string by concatenating the wildcards: '%' + word1 + '%' + word2 + '%'. This pattern searches for word1, followed by any characters, followed by word2, followed by any characters.
3. We use Database.query(query, searchPattern) to execute the dynamic SOQL query. Using bind variables (:searchPattern) is crucial for security and performance, as it prevents SOQL injection vulnerabilities.

Important Note on String.format(): While String.format() is useful for interpolating simple strings, it's not designed for creating complex LIKE patterns. Manually constructing the searchPattern string as shown above is the correct approach for LIKE clauses with dynamic wildcards.

Handling Edge Cases and Variations

1. Whitespace Between Words:

The % wildcard is very forgiving with whitespace. If you search for 'New%York', it will match "New York", "New York" (multiple spaces), and "New York" (newline). If you specifically want to match exactly one space between two words, you can use the underscore wildcard:

WHERE MailingAddress LIKE '%Street_Avenue%';

This would only match "Street Avenue" and not "Street Avenue" or "StreetX123Avenue". However, for the general 'in-between words' requirement, % is usually preferred for its flexibility.

2. Words at the Beginning or End of the String:

Our pattern '%' + word1 + '%' + word2 + '%' inherently handles cases where word1 might be at the very beginning or word2 at the very end. The leading and trailing % ensure that anything before word1 and anything after word2 is also considered.

3. Searching for a Phrase with Internal Wildcards:

If your search words themselves contain the % character (which is rare for typical business data but possible if the data was entered programmatically), you would need to escape the % character. In SOQL, the escape character is typically a backslash (\). So, if you wanted to search for 'Project%A' followed by 'Phase B', the pattern would be 'Project\%A%Phase B%'.

However, for the common use case of matching words between other words, you don't need to worry about escaping the wildcards used within the LIKE pattern itself.

4. Performance Considerations:

While powerful, LIKE queries with leading wildcards (%word%) can be less performant than those with trailing wildcards (word%) because they often cannot utilize standard indexes effectively. If your searches frequently involve leading wildcards or in-between wildcards, consider the following:

• Data Archiving/Summarization: If historical data becomes unwieldy, consider archiving it or creating summary fields that are easier to query.
• Text Search Tools: For very large datasets or complex text analysis, consider integrating with external search engines (like Elasticsearch) or using Salesforce's native text search capabilities if they meet your needs.
• Custom Indexing: In some advanced scenarios, custom indexing might be an option, but it's often complex and comes with its own considerations.

For most common scenarios, the LIKE operator with wildcards is perfectly adequate and provides a flexible way to query your Salesforce data.

Key Takeaways

• The SOQL LIKE operator is essential for pattern matching in string fields.
• The % wildcard represents zero or more characters, making it ideal for flexible searches.
• To match text between two specific words (e.g., WordA and WordB), use the pattern '%' + WordA + '%' + WordB + '%'.
• SOQL LIKE is generally case-insensitive for standard text fields.
• Dynamic SOQL queries in Apex should use bind variables (:variableName) to prevent SOQL injection and improve performance.
• Be mindful of performance implications, especially with leading wildcards, and consider alternative solutions for extremely large datasets or complex text-searching requirements.