The boolean full-text search capability supports the following operators:
Indicates that the word or phrase on each side of the NEAR or ~ operator must occur in a document for a match to be returned. Several proximity terms can be chained, as in a NEAR b NEAR c or a ~ b ~ c. Chained proximity terms must all be in the document for a match to be returned.
For example, 'fox NEAR chicken' and 'fox ~ chicken' would both return any documents in the specified column that contain both "fox" and "chicken". In addition, rank is calculated for each document based on the proximity of "fox" and "chicken". For example, if a document contains the sentence, "The fox ate the chicken," its ranking would be high.
A phrase that is enclosed within double quote characters ("search phrase") matches only search results that contain the phrase literally, as it was typed. Nonword characters need not be matched exactly: Phrase searching requires only that matches contain exactly the same words as the phrase and in the same order. For example, "test phrase" matches "test, phrase".
A logical operator (e.g. AND, AND NOT, OR) should be placed between every two words (no need to do this within a phrase enclosed to quotes). If there is no logical operator between two words, it is assumed that OR should be used. For example:
Indicates that the two contains search conditions must be met for a match. The ampersand symbol (&) may be used instead of the AND keyword to represent the AND operator. AND always has higher proirity than OR.
Indicates that the second search condition must not be present for a match. The ampersand followed by the exclamation mark symbol (&!) may be used instead of the AND NOT keyword to represent the AND NOT operator.
Indicates that either of the two contains search conditions must be met for a match. The bar symbol (|) may be used instead of the OR keyword to represent the OR operator.
If the phrase contains no words that are in the index, the result is empty. For example, if all words are either stopwords or shorter than 3 letters, the result is empty.
The following examples demonstrate some search strings that use boolean full-text operators:
Find items that contain at least one of the two words.
Find items that contain both words.
Find items that contain the word "apple" but not "macintosh".
Find items that contain the exact phrase "some words" (for example, items that contain "some words of wisdom" but not "some noise words"). Note that the " characters that enclose the phrase are operator characters that delimit the phrase. They are not the quotes that enclose the search string itself.