SOQL CONTAINS vs LIKE vs INCLUDES: Which Operator to Use

SOQL has no CONTAINS keyword. Three different tools cover the job, and the field type decides which one you can use. Pick the wrong one and Salesforce does not run a slow query, it rejects the query outright. Every error message below was returned by a live org.

Decision table

Field type	"Contains" query	"Does not contain"
Text, Text Area (255), Email, Phone, URL	LIKE '%text%'	(NOT field LIKE '%text%')
Picklist (single-select)	LIKE '%text%' or IN (...)	(NOT field LIKE ...) or NOT IN (...)
Multi-select picklist	INCLUDES ('Value')	EXCLUDES ('Value')
Long Text Area, Rich Text	SOSL FIND	Not possible in SOQL
Several fields or objects at once	SOSL FIND	Filter the SOSL result in Apex
Number, Date, Checkbox	Use =, >, <, IN	!=, NOT IN

LIKE: contains for text fields

LIKE with % on both sides is the SOQL version of contains. % matches any run of characters, _ matches exactly one:

// Contains
SELECT Id, Name FROM Account WHERE Name LIKE '%consulting%'

// Starts with
SELECT Id, Name FROM Account WHERE Name LIKE 'Acme%'

// Ends with
SELECT Id, Name FROM Account WHERE Name LIKE '%Inc.'

// Does not contain
SELECT Id, Name FROM Account WHERE (NOT Name LIKE '%test%')

LIKE ignores case. Leave out the wildcards and you get a case-insensitive exact match, which is the usual reason a contains query returns nothing.

Two things to know before you ship it:

• A leading % cannot use an index. On a large object, add another selective filter or anchor the pattern at the start.
• The negated form keeps blank values. (NOT Website LIKE '%test%') returns accounts with no website at all. Add AND Website != null if you do not want them.

The full set of wildcard, escaping and bind-variable patterns is in the SOQL LIKE and NOT LIKE guide.

INCLUDES: contains for multi-select picklists

A multi-select picklist does not accept LIKE:

SELECT Id FROM Contact WHERE BuyerAttributes LIKE '%Champion%'
// ERROR: invalid operator on multipicklist field

Use INCLUDES and EXCLUDES, which compare individual values:

// Has Golf
SELECT Id FROM Contact WHERE Interests__c INCLUDES ('Golf')

// Has Golf AND Tennis: one string, semicolon-separated
SELECT Id FROM Contact WHERE Interests__c INCLUDES ('Golf;Tennis')

// Has Golf OR Tennis: separate strings
SELECT Id FROM Contact WHERE Interests__c INCLUDES ('Golf', 'Tennis')

// Does not have Golf
SELECT Id FROM Contact WHERE Interests__c EXCLUDES ('Golf')

The semicolon rule is the part people get backwards. 'Golf;Tennis' in one string means the record must hold both values. 'Golf', 'Tennis' as two strings means either one is enough. You can mix them: INCLUDES ('Golf;Tennis', 'Sailing') means "Golf and Tennis, or Sailing".

INCLUDES matches whole values, so INCLUDES ('New') does not match a value called New York. That is the behavior you want, and it is why string matching would be wrong here even if Salesforce allowed it.

EXCLUDES returns records where the field is empty. In the test org, every contact had a blank BuyerAttributes, and EXCLUDES ('Champion') returned all 11.

SOSL: long text fields and searches across objects

Long Text Area and Rich Text fields cannot be filtered in SOQL:

SELECT Id FROM Account WHERE Description LIKE '%renewal%'
// ERROR: field 'Description' can not be filtered in query call

SOSL searches them through the search index, and it can cover several objects in one call:

List<List<SObject>> results = [
  FIND 'renewal'
  IN ALL FIELDS
  RETURNING
    Account(Id, Name),
    Opportunity(Id, Name, StageName)
  LIMIT 50
];

List<Account> accounts = (List<Account>) results[0];
List<Opportunity> opportunities = (List<Opportunity>) results[1];

SOSL is a search, not a filter. It tokenizes words, ranks results and only sees records once they are indexed, so a record saved a moment ago may not appear yet. When you need an exact rule, use SOSL to find candidates, then check the field in Apex with String.contains().

Common mistakes

• Forgetting the wildcards. LIKE 'consulting' is an exact match.
• Using LIKE on a multi-select picklist. It is a parse-time error, not an intermittent one. Use INCLUDES.
• Swapping the AND and OR forms of INCLUDES. One string with semicolons is AND; separate strings are OR.
• Filtering a Long Text Area in WHERE. It cannot be done in SOQL. Use SOSL.
• Writing NOT LIKE. SOQL only accepts (NOT field LIKE '...').