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.

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.