Boolean searches

A Boolean search enables you to combine the keywords to create more complicated clues.

To specify documents that contain both the words multimedia and document but not the word video, use a Boolean search, as shown in the following example:

SELECT id, description FROM videos
    WHERE etx_contains (description, 
    Row('multimedia & editor & !video', 
    'SEARCH_TYPE = BOOLEAN_SEARCH'));

The Boolean operators & and ! are used to build the Boolean expression that you want. You specify a Boolean search by setting the SEARCH_TYPE tuning parameter to BOOLEAN_SEARCH.

If you have created an etx index with PHRASE_SUPPORT enabled, you can specify Boolean phrases in your etx_contains() syntax, as shown in the following example:

SELECT id, description FROM videos
    WHERE etx_contains (description,
    Row ('vanilla wafers | chocolate chip cookies',
         'SEARCH_TYPE = BOOLEAN_SEARCH'));

This query returns relevant records that contain either of the phrases vanilla wafers or chocolate chip cookies.

Important: You encounter an error if you specify a Boolean phrase search on a column that has not been indexed with PHRASE_SUPPORT.

To perform a Boolean search for clues that themselves contain a character that represents a Boolean operator, escape the character with backslash, as shown in the following example:

SELECT id, description FROM videos
    WHERE etx_contains (description,
    Row ('Lord \& Taylor | Nordstrom',
         'SEARCH_TYPE = BOOLEAN_SEARCH'));

This query returns relevant records that contain either of the names Lord & Taylor or Nordstrom. If the Boolean & operator are not escaped, the query returns documents that include the words Lord and Taylor or documents that include the word Nordstrom.

You can escape the Boolean operators &, |, !, and ^ by using the backslash.

Important: The search engine always applies a document score of 100 to all rows returned by a Boolean search, even when the Boolean search is combined with a pattern search. The search engine cannot assign a meaningful relative ranking to hits since a Boolean clue might be composed of many parts, and each hit might result from a different part of the clue. The search engine, therefore, scores Boolean searches arbitrarily—100—even in cases where it might seem to a user that a meaningful score could be applied.