SOQL LIKE with Wildcards: In-Between Word Matching

SOQL filters most things well enough, but sometimes you need records where a specific piece of text appears between two other known words. The standard LIKE operator, combined with the wildcard character %, does that, with a few edges worth knowing about. This guide covers SOQL LIKE with wildcards for in-between word matching and the Apex query logic around it.

Understanding SOQL LIKE and wildcards

The LIKE operator in SOQL does pattern matching on string fields, usually with wildcards standing in for one or more characters. Two wildcards are available:

• % (percent sign) represents zero or more characters. This is the workhorse for flexible matching.
• _ (underscore) represents exactly one character. Useful, though less common for in-between word matching.

'In-between word matching' typically means you know a starting word and an ending word, and you want records where some text sits between them. You might want Account descriptions that contain "premium" followed by "customer" with any characters, or no characters, in between.

Take a simple example. Suppose we have a custom Product__c object with a Description__c field, and we want products that mention "limited edition" somewhere in their description.

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. Being more precise means requiring "limited edition" to appear between two other specific terms, such as descriptions that mention "available" followed by "now" with something in between.

Matching text between two known words

To find text between two specific words with SOQL LIKE and wildcards, 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.

Use the Account object as an example. Suppose we want 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 returns 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".

Some examples:

• '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

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 often construct SOQL queries dynamically, from user input or other programmatic conditions, and that is where LIKE with wildcards earns its keep.

Consider a scenario where you need to search Contact records on a partial phrase found in their MailingAddress field. Say we want 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, the words we want to find in sequence.
2. We build the searchPattern string by concatenating the wildcards: '%' + word1 + '%' + word2 + '%'. That 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. Bind variables (:searchPattern) matter here for security and performance, because they prevent SOQL injection vulnerabilities.

A note on String.format(): it is useful for interpolating simple strings, but it is not designed for creating complex LIKE patterns. Constructing the searchPattern string by hand, as shown above, is the correct approach for LIKE clauses with dynamic wildcards.

Handling edge cases and variations

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, use the underscore wildcard:

WHERE MailingAddress LIKE '%Street_Avenue%';

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

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

Search words that themselves contain the % character are the exception, which is rare for typical business data but possible if the data was entered programmatically. There you have to escape the % character. In SOQL, the escape character is typically a backslash (\). If you wanted to search for 'Project%A' followed by 'Phase B', the pattern would be 'Project\%A%Phase B%'. For the common case of matching words between other words, you don't need to worry about escaping the wildcards used *within* the LIKE pattern itself.

Performance is the last thing to weigh. 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:

• If historical data becomes unwieldy, archive it or create summary fields that are easier to query.
• 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.
• In some advanced scenarios, custom indexing might be an option, though it is often complex and comes with its own considerations.

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