WHERE

By default, a SOSL query on an object retrieves all rows that are visible to the user, including archived rows. To limit the search, you can filter the search result by specific field values.

The conditionExpression of the WHERE clause uses the following syntax:

1fieldExpression [logicalOperator fieldExpression2 ... ]

You can add multiple field expressions to a condition expression by using logical operators.

The condition expressions in SOSL FIND statements appear in bold in these examples:

• FIND {test} RETURNING Account (id WHERE createddate = THIS_FISCAL_QUARTER)
• FIND {test} RETURNING Account (id WHERE cf__c includes('AAA'))

You can use parentheses to define the order in which fieldExpressions are evaluated. You must specify parentheses when nesting operators. However, multiple operators of the same type don’t need to be nested. In the following example, the expression is true if fieldExpression1 is true and either fieldExpression2 or fieldExpression3 are true.

1fieldExpression1 AND (fieldExpression2 OR fieldExpression3)

However, the following expression is true if either fieldExpression3 is true or both fieldExpression1 and fieldExpression2 are true.

1(fieldExpression1 AND fieldExpression2) OR fieldExpression3

A fieldExpression uses the following syntax:

1_fieldName comparisonOperator value

where:

The following table lists the comparisonOperator values that are used in fieldExpression syntax. Comparisons on strings are case-insensitive.

The LIKE operator includes support for:

• The % and _ wildcards.

  • The % wildcard matches zero or more characters.
  • The _ wildcard matches exactly one character.

• The escaping of special characters % or _.

Note: Don’t use the backslash (\) character in a search except to escape a special character. See Quoted String Escape Sequences.

This example query matches Appleton, Apple, and Appl, but not Bappl:

1FIND {test} IN ALL FIELDS RETURNING Contact(AccountId, FirstName, LastName WHERE LastName LIKE 'appl%')

Example IN query:

1FIND {test} IN ALL FIELDS RETURNING Account(Name WHERE BillingState IN ('California', 'New York'))

Example NOT IN query:

1FIND {test} IN ALL FIELDS RETURNING Account(Name WHERE BillingState NOT IN ('California', 'New York'))

Note: The logical operator NOT is unrelated to this comparison operator.

The following table lists the logical operator values that are used in fieldExpression syntax:

Example OR query:

1FIND {test} IN ALL FIELDS RETURNING Contact(Id WHERE LastName = 'Young' OR Account.Name = 'Quarry')

Example NOT query:

1FIND {test} IN ALL FIELDS RETURNING Account(Name WHERE AnnualRevenue > 0 AND (NOT Type = 'Prospect'))

You can use the following escape sequences with SOSL:

If you use a backslash character in any other context, an error occurs.

Example WHERE clause with fiscal quarter filter:

1FIND {test}
2    RETURNING Account (id WHERE createddate = THIS_FISCAL_QUARTER)

Example WHERE clause with multi-select picklist includes:

1FIND {test}
2    RETURNING Account (id WHERE cf__c includes('AAA'))

Example WHERE clause with multiple objects and ORDER BY:

1FIND {test}
2    RETURNING Account (id), User(Field1,Field2 WHERE Field1 = 'test' order by id ASC, Name DESC)

Example WHERE clause searching contacts, users, and accounts:

1FIND {test} IN ALL FIELDS
2    RETURNING Contact(Salutation, FirstName, LastName, AccountId WHERE Name = 'test'),
3        User(FirstName, LastName),
4        Account(id WHERE BillingState IN ('California', 'New York'))

Example WHERE clause with complex OR/AND conditions:

1FIND {test}
2    RETURNING Account (id WHERE (Name = 'New Account')
3        or (Id = '001z00000008Vq7'
4        and Name = 'Account Insert Test')
5        or (NumberOfEmployees < 100 or NumberOfEmployees = null)
6        ORDER BY NumberOfEmployees)

To search for a Salesforce Knowledge article by ID:

1FIND {tourism}
2    RETURNING KnowledgeArticleVersion (Id, Title WHERE id = 'ka0D0000000025eIAA')

To search for multiple Salesforce Knowledge articles by ID:

1FIND {tourism}
2    RETURNING KnowledgeArticleVersion
3        (Id, Title WHERE id IN ('ka0D0000000025eIAA', 'ka0D000000002HCIAY'))

To search for “San Francisco” in all fields of all My_Custom_Object__c objects that have a geolocation or address location within 500 miles of the latitude and longitude coordinates 37 and 122, respectively:

1FIND {San Francisco}
2    RETURNING My_Custom_Object__c (Id
3        WHERE DISTANCE(My_Location_Field__c,GEOLOCATION(37,122),'mi') < 100)

See Also

• SOQL and SOSL reference: Date Formats and Date Literals in WHERE

j

Introduction to SOQL and SOSL

Salesforce Object Query Language (SOQL)

Salesforce Object Search Language (SOSL)

Typographical Conventions in This Document

SOSL Limits on Search Results

SOSL Limits on External Object Search Results

SOSL Syntax

Example Text Searches

convertCurrency()

FIND {SearchQuery}

FORMAT()

IN SearchGroup

LIMIT n

OFFSET n

ORDER BY Clause

RETURNING FieldSpec

toLabel(fields)

Update an Article’s Keyword Tracking with SOSL

Update an Article’s Viewstat with SOSL

USING Listview=

WHERE

WITH DATA CATEGORY DataCategorySpec

WITH DivisionFilter

WITH HIGHLIGHT

WITH METADATA

WITH NETWORK NetworkIdSpec

WITH PricebookId

WITH SNIPPET

WITH SPELL_CORRECTION

Previous Versions