Boolean search

Using the AND operator in full text searches

The AND operator matches a row if it contains both of the terms specified on either side of the AND. You can also use an ampersand (&) for the AND operator. If terms are specified without an operator between them, AND is implied.

The order in which the terms are listed is not important.

For example, each of the following statements finds rows in MarketingInformation.Description that contain the term fabric and a term that begins with ski:

SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'ski* AND fabric' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric & ski*' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'ski* fabric' );

Using the OR operator in full text searches

The OR operator matches a row if it contains at least one of the specified search terms on either side of the OR. You can also use a vertical bar (|) for the OR operator; the two are equivalent.

The order in which the terms are listed is not important.

For example, either statement below returns rows in the MarketingInformation.Description that contain either the term fabric or a term that starts with ski:

SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'ski* OR fabric' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric | ski*' );

Using the AND NOT operator in full text searches

The AND NOT operator finds results that match the left argument and do not match the right argument. You can also use a hyphen (-) for the AND NOT operator; the two are equivalent.

For example, the following statements are equivalent and return rows that contain the term fabric, but do not contain any terms that begin with ski.

SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric AND NOT ski*' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric -ski*' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric & -ski*' );

Combining different boolean operators

The boolean operators can be combined in a query string. For example, the following statements are equivalent and search the MarketingInformation.Description column for items that contain fabric and skin, but not cotton:

SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'skin fabric -cotton' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric -cotton AND skin' );

The following statements are equivalent and search the MarketingInformation.Description column for items that contain fabric or both cotton and skin:

SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'fabric | cotton AND skin' );
SELECT * 
   FROM MarketingInformation 
   WHERE CONTAINS ( MarketingInformation.Description, 'cotton skin OR fabric' );

Grouping terms and phrases

Terms and expressions can be grouped with parentheses. For example, the following statement searches the MarketingInformation.Description column for items that contain cotton or fabric, and that have terms that start with ski.

SELECT ID, Description FROM MarketingInformation
   WHERE CONTAINS( MarketingInformation.Description, '( cotton OR fabric ) AND shi*' );

Searching across multiple columns

You can perform a full text search across multiple columns in a single query, as long as the columns are part of the same text index.

SELECT * 
   FROM t 
      WHERE CONTAINS ( t.c1, t.c2, 'term1|term2' );

SELECT * 
   FROM t 
   WHERE CONTAINS( t.c1, 'term1' ) 
      OR CONTAINS( t.c2, 'term2' );

The first query matches if t1.c1 contains term1, or if t1.c2 contains term2.

The second query matches if either t1.c1 or t1.c2 contains either term1 or term2. Using the contains in this manner also returns scores for the matches.