We help IT Professionals succeed at work.

Difference between "CONTAINS" and "FREETEXT" in FULL TEXT SEARCH - SQL Server

Hi,

Can you please let me know in simple terms the

Difference between "CONTAINS" and "FREETEXT" in FULL TEXT SEARCH ?

Please provide sample code to understand it better. No articles please !

Thanks
Comment
Watch Question

Christopher GordonSenior Developer Analyst

Commented:
"CONTAINS" looks for one word.  Search variable should not be a string with spaces.  Just one word.

"FREETEXT" looks for multiple words. Search variable can have multiple words and it will search on all of them
Top Expert 2012
Commented:
>>"CONTAINS" looks for one word.  Search variable should not be a string with spaces.  Just one word.<<
Actually, no. CONTAINS can be a single word, many words, a prefix search or a phrase.  There is also a whole host of other functionality that is best covered by reading up in BOL.

The key difference between CONTAINS and FREETEXT is FREETEXT searches for the meaning of words not just the values.  But CONTAINS also allows the use of THESAURUS, which makes it similar to FREETEXT.
Christopher GordonSenior Developer Analyst
Commented:
declare @searchstring = 'purple monkey dishwasher'

ex.
select * from mytable
where             freetext (ProductDescr, @searchString)

will search for all three words


declare @searchstring = 'purple'

ex.
select * from mytable
where             contains (ProductDescr, @searchString)
Top Expert 2012

Commented:
SinghAmandeep,

Unfortunately, you have chosen to copy and paste without giving any credit from here:
Comparing Full-Text Functions and Full-Text Predicates
http://msdn.microsoft.com/en-us/library/ms142494(SQL.90).aspx

This is a direct violation of EE Guidelines. See here:
http://www.experts-exchange.com/help.jsp#hs=23&hi=605
About copyrights
Copying and pasting a web page or other document is considered copyright infringement in most cases. If you want to refer to someone else's work the preferred method is to post the link with a brief description of it. Posts that consist entirely of work copied and pasted from other sites will generally be deleted unless it's from your own work.


I have asked a Moderator to review.

Author

Commented:
Any more comments ?

Thanks
Christopher GordonSenior Developer Analyst

Commented:
Anything specific you're looking for?  

acperkins provided you with a good high level explanation.  
I provided you some high level code on how you'd use in T-SQL.

Are we missing something? :)  

Thanks!
Christopher GordonSenior Developer Analyst

Commented:
Anything specific you're looking for?  

acperkins provided you with a good high level explanation.  
I provided you some high level code on how you'd use in T-SQL.

Are we missing something? :)  

Thanks!