Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Expand search capabilities with the SQL Server FREETEXT command


By:   |   Read Comments   |   Related Tips: More > Full Text Search


ALERT: Share your SQL Server knowledge and become a MSSQLTips author


Problem
I have been reading about the Full Text Search capabilities in SQL Server 2005 and your tips have been a big help to improve how we query our data.  Thus far converting our core queries to leverage Full Text Search has been a big help.  I have been seeing information about the FREETEXT command and I would like to learn how to use this FREETEXT command versus the CONTAINS command.  Should I use the FREETEXT command over the CONTAINS command or vice versa?  Can you also provide some examples of using the FREETEXT command to learn about the variety of capabilities available?

Solution
Yes - Let's take a look at the comparison first and then we can work through a few different examples.  The FREETEXT command is another alternative to access the data indexed by Full Text Search.  In general the FREETEXT command provides the ability to search for a matched term based on the meaning of the terms as opposed to the exact character string.  At a high level, this commands finds matches based on separating the string into individual words, determining inflectional versions of the word and using a thesaurus to expand or replace the term to improve the search.

Now let's compare the FREETEXT functionality with the CONTAINS command.  The CONTAINS command uses exact match or fuzzy logic to perform the matches with a single word or a phrase.  In addition, the words near another word can be found as well as performing a weighted match of multiple words where each word has a weight as compared to the others that are searched.  Check out CONTAINS (Transact-SQL) for a explanation on the CONTAINS command.

Depending on the search you are performing dictates which Full Text Search command you should use.  Keep in mind that FREETEXT and CONTAINS are only two of the four commands available.  The other two commands are CONTAINSTABLE and FREETEXTTABLE.  The comparison between the four commands will be saved for a future tip since it is fairly involved explanation that should include examples. 

Until that point in time, here is one data point to consider: according to SQL Server 2005 Books Online FREETEXT (Transact-SQL) "Full-text queries using FREETEXT are less precise than those full-text queries using CONTAINS. The SQL Server full-text search engine identifies important words and phrases. No special meaning is given to any of the reserved keywords or wildcard characters that typically have meaning when specified in the <contains_search_condition> parameter of the CONTAINS predicate."  Based on my testing, when the basic terms are queried with either command similar results are returned, so the precise factor for simple queries seems less of an issue.  For complex searches the CONTAINS command wins hands down with the ability to use wild cards, NEAR statements, etc. As such, if the flexibility of the search is built into the front end application then the highest level of flexibility on the back end, between the FREETEXT and CONTAINS commands, tips the scales toward the CONTAINS command.

FREETEXT Examples

Let's take a look at the command reference and a few FREETEXT examples to use as a point of reference and comparison with the other Full Text Search tips on MSSQLTips.com.

FREETEXT Command Syntax

FREETEXT ( { column_name | (column_list) | * } , 'freetext_string' [ , LANGUAGE language_term ] )

Source - SQL Server 2005 Books Online (FREETEXT (Transact-SQL))

Simple FREETEXT Command

USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE FREETEXT(*, 'nut screw washer');
GO
 

Literal Search with the FREETEXT Command

USE AdventureWorks;
GO
SELECT *
FROM Production.Product
WHERE FREETEXT(*, '"flat washer"');
GO
 

FREETEXT Command with Input Parameters

USE AdventureWorks;
GO
DECLARE @SearchWord nvarchar(30);
SET @SearchWord = N'nut screw washer';
SELECT *
FROM Production.Product
WHERE FREETEXT(*, @SearchWord);
GO

*** NOTE *** - As a point of reference, use an nvarchar data type to prevent an implicit conversion.

Next Steps

  • If you have difficult character based search needs and if you have not checked out Full Text Search on one of your development or test environments, be sure to check it out to see if your search capabilities will be expanded with a minimal amount of effort.
  • As you begin your testing, be sure to check out the FREETEXT versus CONTAINS commands to determine which command will meet your needs.  You be the judge on which command is better to use in your applications.
  • Check out these Full Text Search on MSSQLTips.com:

 



Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools