CONTAINS Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 5)

This is fifth part of the series of Integrated Full Text Search in Microsoft SQL Server 2008. If you want to study the topic in depth, Please read my past four articles in this series. Below are the links of the same.

 Introduction of iFTS (Integrated Full Text Search – iFTS in SQL Server 2008 Part 1)  

 Full Text Catalog and Full Text Index (Integrated Full Text Search – iFTS in SQL Server 2008 Part 2)

 FREETEXT Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 3)

FREETEXTTABLE Predicate (Integrated Full Text Search – iFTS in SQL Server 2008 Part 4)

We have already looked at FREETEXT and FREETEXTTABLE predicate in iFTS using Microsoft SQL Server 2008. Now, this is time to look at one more interesting predicate “CONTAINS” in iFTS.

CONTAINS is a more advanced version of FREETEXT predicate. It can accept column(s), search condition like FREETEXT and search simple words, other than that, it can search close words to the word specified into search criteria, thesaurus, synonyms, inflectional words. CONTAINS predicate don’t automatically search inflectional words but you have to use FORMSOF clause with it.

Let us see some practical example to make concept clearer.

Note: I am using the same table & data which I have created in my past articles. So if you are directly landed to this article, kindly get the create table script from second article.

--you won't get any data as inflectional words are not searched by default

--NOTE: we have Volatiles as a plural and in our data, it is singular.

SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,'Volatiles')

--you will get results as you are making exact search

SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,'Volatile')

--enabling inflectional word search so you can get data with singular forms of words

--even if you put plural in search criterial and vice versa

SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N'FORMSOF(INFLECTIONAL,Volatiles)')

--now we knew that we have three row as a results when we search Volatile word

--now I want to be more specific, I just want those row which has "Volatile" near "Again"

--you will get only one row as a results

SELECT * FROM FTSTest WHERE CONTAINS(TestDescription,N'Volatile NEAR Again')

 

CONTAINS is really lot more powerful than FREETEXT search. Isn’t it?

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com