Combining Full-text Search Operators Using AND, OR, and 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 CONTAINS (*, '("ice skating" or hockey) AND NOT olympics')