Searching for Specific Word or Phrase (Simple Term)

You can use the CONTAINS predicate to search a table for a specific phrase.

For example, if you want to search the ProductReview table in the AdventureWorks2008R2 database to find all comments about a product with the phrase "learning curve", you could use the CONTAINS predicate as follows.

USE AdventureWorks2008R2;
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "learning curve" ');

The CONTAINS predicate uses functional notation in which the first parameter is the name of the column or list of columns being searched, and the second parameter is a full-text search condition. The search condition, in this case "learning curve", can be quite complex and can be composed of one or more terms


The full-text search queries are case-insensitive. However, in Japanese, there are multiple phonetic orthographies in which the concept of orthographic normalization is akin to case insensitivity (for example, kana = insensitivity). This type of orthographic normalization is not supported.