This is sixth 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 five 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)

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

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 listed above.

 Since we had already looked at FREETEXTTABLE, CONTAINSTABLE is not something new. It is also work at TVF but it has bit more functionality than FREETEXTTABLE. You have to provide table name, column(s) name and search condition. CONTAINSTABLE can accept all search condition which is valid in CONTAINS predicate.

CONTAINSTABLE also return KEY and RANK column like FREETEXTTABLE which you can join with your original table. You can refer FREETEXTTABLE’s article for more details.

Let us see some examples for CONTAINSTABLE in Microsoft SQL Server 2008.

--this T-SQL will return only key value and rank

SELECT

[KEY],

[RANK]

FROM CONTAINSTABLE

(

FTSTest,

TestDescription,

N'FORMSOF(INFLECTIONAL, volatiles)'

);

GO

--this is sample search of CONTAINSTABLE

--with FORMSOF clause which we used in CONTAINS also in previous article

Select t2.* FROM CONTAINSTABLE

(

FTSTest,

TestDescription,

N'FORMSOF(INFLECTIONAL, volatiles)'

) as t1 join FTSTest t2 on t1.[key]=t2.Id

GO

--there is one more useful clause, ISABOUT, which you can use with CONTAINS and CONTAINSTABLE

--You can give weight of the search word between 0.0 to 1.0

--search word with greater weight comes up with higher rank so that you can sort it easily.

SELECT

ct.[RANK],

ct.[KEY],

pm.[TestName],

pm.[TestDescription]

FROM CONTAINSTABLE

(

ftsTest,

TestDescription,

N'ISABOUT(volatile WEIGHT(0.1), General WEIGHT(1.0))'

) ct

INNER JOIN FTSTest pm

ON ct.[KEY] = pm.ID

ORDER BY ct.[RANK] DESC;

GO

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

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

I have covered basic information about Full Text Search at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005.html

Configuring and maintaining Full Text Search Catalog at http://ritesh-a-shah.blogspot.com/2009/03/full-text-search-in-sql-server-2005_16.html

And now finally I am going to show you how to select data after configuring the catalog with Full Text Search in Microsoft SQL Server 2005.

Contains:

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.LoginID,'"*je*"')

I have used Double Quote and Asterisk as wild card as Full Text Search uses standard DOS convention as a wild card. Above query will return all the records whose “LoginID” column contain “je” anywhere in the data.

Note: I gave “LoginID” column name in CONTAINS but if you wish to search “je” in every column which has been added to index while creating catalog, you can use “*” there.

Use Adventureworks

select * from HumanResources.Employee

WHERE Contains(HumanResources.Employee.*,'"*je*"')

FreeText:

Use Adventureworks

select * from HumanResources.Employee

WHERE freetext(HumanResources.Employee.*,'jolynn0 david0')

Contains uses exact word search while FreeText uses fuzzy or approximate word search. Above query will show all the records which either contain “jolynn0” or “david0”.

Reference: Ritesh Shah