Term and phrase search

Term searching

In the sample database, a text index called MarketingTextIndex has been built on the Description column of the MarketingInformation table. The following statement queries the MarketingInformation.Description column and returns the rows where the value in the Description column contains the term cotton.

SELECT ID, Description 
   FROM MarketingInformation
   WHERE CONTAINS ( Description, 'cotton' );

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

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

The next example queries the MarketingInformation table for items that have the term cotton the Description column, and shows the score for each match.

SELECT ID, ct.score, Description
   FROM MarketingInformation CONTAINS ( MarketingInformation.Description, 'cotton' ) as ct
   ORDER BY ct.score DESC;

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 terms in the specified order and relative positions.

You cannot specify CONTAINS keywords, such as AND or FUZZY, as terms to search for unless you place them inside a phrase (single term phrases are allowed). For example, the statement below is acceptable even though NOT is a CONTAINS keyword.

SELECT * FROM table-name CONTAINS ( Remarks, '"NOT"' );

With the exception of asterisk, special characters are not interpreted as special characters when they are in a phrase.

Phrases cannot be used as arguments for proximity searches.

The following statement queries MarketingInformation.Description for the phrase "grown cotton", and shows the score for each match:

SELECT ID, ct.score, Description
   FROM MarketingInformation CONTAINS ( MarketingInformation.Description, '"grown cotton"' ) as ct
   ORDER BY ct.score DESC;