CONTAINS Predicate in SQL

CONTAINS is a predicate used to perform a full-text search in one or more full-text indexed columns.

Let's look into an example,

SELECT [Description]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS([Description], 'discount'); /* CONTAINS(Column_Name, Search_Text) */

The above statement returns the data which has discount word in column [Description]. 

And CONTAINS predicate is not case sensitive.

CONTAINS text search is very quick and easy to search a word or phrase. 

Note: CONTAINS full-text search works in a full-text indexed column. If you face any errors, have a look at this link here.

Using CONTAINS with <multiple columns>

SELECT [Description],[Type]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS(([Description],[Type]), 'discount'); 
/* CONTAINS((Column_Name1,Column_Name2,...), Search_Text) */

The above CONTAINS query searches the word discount in both [Description] and [Type] columns and returns the result that matches the exact word.

Using CONTAINS with <Phrase>

SELECT [Description]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS([Description], 'Discount OR Sale'); /* CONTAINS(Column_Name, Search_Text) */

This example returns the special offers which contains either the phrase Discount or Sale in Description column.

Using CONTAINS with <Prefix>

SELECT [Description]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS([Description], 'Sport*'); /* CONTAINS(Column_Name, Search_Text) */

This query returns the special offers with a prefix of the word Sport in the Description column.

Using CONTAINS with <NEAR>

NEAR used to search for words which is close to another word

SELECT [Description]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS([Description], 'NEAR((Silver,Sale),2,TRUE)'); /* CONTAINS(Column_Name, Search_Text) */

This query searches the word Silver within 2 terms of the word Sale in the column Description.

Using CONTAINS with <Weight>

SELECT [Description]
FROM [Sales].[SpecialOffer]
WHERE CONTAINS([Description], 'ISABOUT(Discount weight(.5),Sale weight(.2))'); 
/* CONTAINS(Column_Name, Search_With_Weight) */

This query searches the words containing Discount or Sale with the specified weights.

Learn more...