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;
GO
SELECT Comments
FROM Production.ProductReview
WHERE CONTAINS(Comments, ' "learning curve" ');
GO

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