Using the CONTAINS Predicate

You can use the CONTAINS predicate to search a database for a specific phrase. Of course, such a query can be written using the LIKE predicate. However, many forms of CONTAINS provide far more text query capabilities than can be obtained with LIKE. Additionally, unlike using the LIKE predicate, a CONTAINS search is always case insensitive.

Note  The full-text search queries behave in a case-insensitive manner for those languages (mostly Latin-based) for which case sensitivity is meaningful. 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.

Assume that you want to search within the Northwind database to find the phrase "bean curd". If you use the CONTAINS predicate, this is a fairly easy query.

USE Northwind 
GO
SELECT Description
FROM Categories
WHERE Description LIKE '%bean curd%'
GO

Or, using CONTAINS:

USE Northwind
GO
SELECT Description
FROM Categories
WHERE CONTAINS(Description, ' "bean curd" ')
GO

The CONTAINS predicate uses functional notation in which the first parameter is the name of the column being searched and the second parameter is a full-text search condition. The search condition, in this case "bean curd", can be quite complex and is made up of one or more terms, which are described later.

The CONTAINS predicate supports complex syntax to search character-based columns for:

• One or more specific words and/or phrases (simple term).

  A word is one or more characters without spaces or punctuation. A valid phrase can consist of multiple words with spaces with or without punctuation between them. For example, croissant is a word, and café au lait is a phrase. Words and phrases such as these are called simple terms.

• Inflectional form of a specific word (generation term).

  For example, search for the inflectional form of the word drive. If various rows in the table include the words drive, drives, drove, driving, and driven, all would be in the result set because each of these can be inflectionally generated from the word drive.

• A word or a phrase where the words begin with specified text (prefix term).

  In case of a phrase, each word within the phrase is considered to be a prefix. For example, the term auto tran* matches automatic transmission and automobile transducer.

• Words or phrases using weighted values (weighted term).

  For example, you want to find a word that has a higher designated weighting than another word. It returns ranked query results.

• A word or phrase close to another word or phrase (proximity term).

  For example, you want to find the rows in which the word ice is near the word hockey or in which the phrase ice skating is near the phrase ice hockey.

A CONTAINS predicate can combine several of these terms by using AND and OR, for example, to find all rows with latte and New York-style bagel in the same full-text enabled database column. Furthermore, terms can be negated by the use of AND NOT, for example bagel and not cream cheese.

When you use CONTAINS, remember SQL Server discards noise words from the search criteria. Noise words are those words such as a, and, is, or the, which can occur frequently but do not really help when searching for specific text.