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

If you have landed directly to this article and wish to have detail information about iFTS, I would recommend you to look at my previous two articles, Part1 and Part2 which covered basic information about iFTS.
FREETEXT predicate plays very crucial role in iFTS search. It is a basic yet very useful mechanism. This predicate searches a word that matches with inflectional forms and thesaurus.  You can use FREETEXT predicate on one column or on list of column inside your Full Text Index.
We have already created Full Text Catalog and Full Text Index along with one sample table in my previous article. We are going to use that table and index herewith in this article.
–this search will be performed on every column
–which are the part of our Full Text Index
–in our case, we have two columns (TestName, TestDescription)
–in our Full Text Index.
Select * from FTSTest where FREETEXT(*,N’Volatile’)
–following search will be performed on perticular columns only
Select * from FTSTest where FREETEXT(TestDescription,N’Volatile’)
Select * from FTSTest where FREETEXT(TestName,N’Volatile’)
–if you will try to use FREETEXT in the column which is
–not part of fullText Index, you will be greeted with error like belo.
–Msg 7601, Level 16, State 3, Line 1
–Cannot use a CONTAINS or FREETEXT predicate on column ‘Id’ because it is not full-text indexed.
Select * from FTSTest where FREETEXT(ID,N’Volatile’)
This is first step into the world of iFTS. See you soon with some more articles in this series only.
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

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

If you have landed directly to this article and wish to have detail information about iFTS, I would recommend you to look at my previous two articles, Part1 and Part2 which covered basic information about iFTS.
FREETEXT predicate plays very crucial role in iFTS search. It is a basic yet very useful mechanism. This predicate searches a word that matches with inflectional forms and thesaurus.  You can use FREETEXT predicate on one column or on list of column inside your Full Text Index.
We have already created Full Text Catalog and Full Text Index along with one sample table in my previous article. We are going to use that table and index herewith in this article.
–this search will be performed on every column
–which are the part of our Full Text Index
–in our case, we have two columns (TestName, TestDescription)
–in our Full Text Index.
Select * from FTSTest where FREETEXT(*,N’Volatile’)
–following search will be performed on perticular columns only
Select * from FTSTest where FREETEXT(TestDescription,N’Volatile’)
Select * from FTSTest where FREETEXT(TestName,N’Volatile’)
–if you will try to use FREETEXT in the column which is
–not part of fullText Index, you will be greeted with error like belo.
–Msg 7601, Level 16, State 3, Line 1
–Cannot use a CONTAINS or FREETEXT predicate on column ‘Id’ because it is not full-text indexed.
Select * from FTSTest where FREETEXT(ID,N’Volatile’)
This is first step into the world of iFTS. See you soon with some more articles in this series only.
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

Full Text Search in SQL Server 2005 Part 3

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