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 capability 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:

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.

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.