Full Text Search for Dummies!!

Posted on 2011-10-05
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
    LVL 39

    Accepted Solution

    Full-text search is fairly large topic in SQL briefly covered at link below in all aspects:

    and in details at:
    with "near" code examples at:
    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
    LVL 1

    Author Comment

    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 39

    Expert Comment

    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:
    LVL 1

    Author Comment

    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 :)

    LVL 1

    Author Closing Comment

    Thanks for your help.
    LVL 39

    Expert Comment

    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)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now