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 CONTAINS (*, '("ice skating" or hockey) AND NOT olympics')