Learn how to a build a cloud-first strategyRegister Now


Full Text Search for Dummies!!

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-05-12
Is there a webpage somewhere that simply explains the uses of CONTAINS and FREETEXT with the RESULTS so I can see what these predicates actually do?  I'm trying to lean the capabilities but the MS docs drive me nuts and I don't have the DBs installed to run most examples.

Also what does 2 words being "near" each other mean?  Is that defined when you set up FTS?

Any "primer" would be appreciated.

We have an application where someone keys in an Organization Name and my boss  wants to know what kind of "fuzzy" search can be done.  If someone keys in:
Int'l Business Machine
International Business Machine
InterMachinal Business Machinee (spelling)

I would like to find our row "International Business Machine".

Thanks in advance
Question by:hefterr
  • 3
  • 3
LVL 40

Accepted Solution

lcohan earned 2000 total points
ID: 36920013
Full-text search is fairly large topic in SQL briefly covered at link below in all aspects:

and in details at: http://msdn.microsoft.com/en-us/library/ms142559.aspx
with "near" code examples at: http://msdn.microsoft.com/en-us/library/ms142583.aspx
and as far "fuzzy" searches, yes that's the feature by design and diference between full-text and LIKE:

if you search for "shoe" in full text the results would be

Author Comment

ID: 36920713
Hi lcohan,
Thanks for the start of the yellow brick road.

There does seem to be an overelap as far as simple string searches (like "shoe" in you example")

I can :
select myfield from mytable where myfield like '%shoe%' and get the same results.

The feature I like (that I have read so far) is searching for "words" and variation of the words (like swim, swimming, swam).

It doesn't seem to pick up on typos though (like when Google indicates "did you mean.....?).

LVL 40

Expert Comment

ID: 36920793
You see I put <<"shoestring"....etc, >> for a reason.

"Is a predicate used in a WHERE clause to search columns containing character-based data types for values that match the meaning and not just the exact wording of the words in the search condition. When FREETEXT is used, the full-text query engine internally performs the following actions on the freetext_string, assigns each term a weight, and then finds the matches.

Separates the string into individual words based on word boundaries (word-breaking).

Generates inflectional forms of the words (stemming).

Identifies a list of expansions or replacements for the terms based on matches in the thesaurus."

Lot more at link below and hope is all clear now:
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 36920882
Is it very difficult to install for someone fairly new to SQL Server administration?  Can you use the SQL Server Management Studio for this?

Just want to know what I am getting into as I work for a very small company with just a few  programmers and I was anointed the DBA :)


Author Closing Comment

ID: 36924172
Thanks for your help.
LVL 40

Expert Comment

ID: 36924519
Full text is not so hard to setup and yes it can be all done pretty easy through SSMS GUI however If you have large table.column(s) that you need to setup for FULL-TEXT searches I suggest you use the GUI and generate/save the scripts from there.
There are a few performance related things plus to be aware and the fact that FULL-TEXT catalogs population is either scheduled (incremental for instance where a row saved in the parent table is not available immediately in a search against the full-text catalog but only after refresh) or by using "change tracking" after its initial full population.
Please see more at:
Performance related (even if one article refers to SQL 2000 many things there are still valid)

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question