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
Note
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.