Term searching

When performing a full text search for a term, the database server uses a text index to find rows that contain the term. Columns specified in a CONTAINS clause must be part of a text index. See Text indexes.

The following statement queries the Description column in the Products table and returns the rows where the value in the Description column contains the term cotton.

SELECT Name, Description 
   FROM Products 
   WHERE CONTAINS ( Description, 'cotton' );

The following example queries the Products table and returns a single value for each row indicating whether the value in the Description column contains the term cotton.

SELECT IF CONTAINS ( Description, 'cotton' )
      THEN 1
      ELSE 0
      ENDIF AS Results
   FROM Products;

The next example queries the Employees table for employees who have the term drive in their street address, and then scores the result of each match.

SELECT * 
   FROM Employees CONTAINS ( Employees.Street, 'Drive' );

For more information about scoring results when CONTAINS is used in the FROM clause of a query, see Scoring full text search results.

Phrase searching

When performing a full text search for a phrase, you enclose the phrase in double quotes. A column matches if it contains the specified sequence of terms. For example, the following statement searches the Remarks column of a fictitious table AccountStatus for the phrase has been notified.

SELECT * 
   FROM AccountStatus CONTAINS ( Remarks, '"has been notified"' );

You cannot directly search for a term that is a keyword in the CONTAINS grammar, such as AND or FUZZY. However, you can search for such a term inside a phrase, and a single-term phrase is allowed. This provides a way to search for a single keyword interpreted as a term. For example, the statement below is acceptable even though NOT is a CONTAINS keyword:

SELECT * 
   FROM AccountStatus CONTAINS ( Remarks, '"not"' );

For a list of the CONTAINS keywords, see CONTAINS search condition.