Combining Full-text Search Operators Using AND, OR, and NOT

You can use parentheses and the Boolean operators (AND, AND NOT, and OR) between search conditions in a CONTAINS predicate. Assume one or more rows in the titles table contains information about favorite recipes and gourmet recipes. To retrieve rows that contain text for either type of recipe, use an OR between the “favorite recipes” and “gourmet recipes” phrases.

USE pubs

GO

SELECT title, notes

FROM titles

WHERE CONTAINS( notes, ' "favorite recipes" OR "gourmet recipes" ' )

GO

  

This example searches for all rows in the titles table in which the title contains cooking, but neither computers nor computer.

USE pubs

GO

SELECT title_id, title, ytd_sales

FROM titles

WHERE CONTAINS( title, ' cooking AND NOT ("computer*" ' )

GO

  

This example obtains a list of product category descriptions in which the description mentions both words beers and ales.

USE Northwind

GO

SELECT CategoryName, Description

FROM Categories

WHERE CONTAINS( Description, ' beers AND ales ' )

GO

  

Phrases and predicates can be combined to search for combinations of words and phrases. For example, you can search for all rows that contain either ice skating or hockey but not references to the Olympics. The WHERE clause for using the CONTAINS predicate looks like this:

WHERE (*, '("ice skating" or hockey) AND NOT olympics')

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.