Using FormsOf in SQL Server Full Text Searching

In the past I’ve talked about some advanced text searching techniques for SQL Server Full Text Searching. Another you can take advantage of is FormsOf. Let’s say we’ve setup a full text search index on the Adventure Works ProductDescription tables’ Description field. (For more info on how to do this, go to the Arcane Lessons page of this site and scroll down to the “Getting Started With SQL Server 2005 Full Text Searching” section.)

FormsOf has two ways to use it, the Inflectional mode and the Thesaurus mode. Let’s look at an example, and then I’ll explain the differences. For this example, we’ll combine data from several tables to get back some meaningful information from the AdventureWorks database.

– Example 1 – FORMSOF INFLECTION

 

select [Name], ProductNumber, [Description]

  from [Production].[Product] p

    , [Production].[ProductDescription] pd

    , [Production].[ProductModelProductDescriptionCulture] pmpdc

 where p.ProductModelID = pmpdc.ProductModelID

   and pmpdc.ProductDescriptionID = pd.ProductDescriptionID

   and CONTAINS(pd.[Description], ‘FORMSOF(Inflectional, light)’ )

 

– Example 2 – FORMSOF THESAURUS

 

select [Name], ProductNumber, [Description]

  from [Production].[Product] p

    , [Production].[ProductDescription] pd

    , [Production].[ProductModelProductDescriptionCulture] pmpdc

 where p.ProductModelID = pmpdc.ProductModelID

   and pmpdc.ProductDescriptionID = pd.ProductDescriptionID

   and CONTAINS(pd.[Description], ‘FORMSOF(Thesaurus, light)’ )

Both examples use the same, I admit, rather bizarre syntax. Inside the string you pass to the CONTAINS clause, you put FORMSOF, with the word Inflectional or Thesaurus, a comma, then the word or phrase you want to search form. In this example I use Contains, but FormsOf also works with FreeText as well.

So what is the difference between Inflectional and Thesaurus? Inflectional finds all of the tenses of a word. For example, if you passed in Start, Inflectional will find Start, Started, and Starting. For nouns, Inflectional finds the single, plural, and possessive forms.

Thesaurus works like you would expect a thesaurus to. It will find variations of a word, Start, Begin, etc. Essentially, words that have the same meaning.

How do these relate to Contains and FreeText? Normally Contains looks for an exact match. FullText matches the meaning, but not the exact words in the query. FullText is very similar to Thesaurus, and even uses the Thesaurus in its work. FullText will break out the search string into its individual words, if there is more than one word. For each word, it generates the inflectional forms of the word, then identifies a list of matches for each word based on the thesaurus. FormsOf(Thesaurus… on the other hand just uses the thesaurus to do the search, without going through the inflectional step.

Using a combination of Contains, FreeText, and FormsOf you can give your users some real flexibility, ranging from exact matches to wide open searches.

About these ads

8 Responses to “Using FormsOf in SQL Server Full Text Searching”

  1. Presenting SQL Server 2005 2008 Full Text Searching at the TechEd 2008 InBetween conference « Arcane Code Says:

    [...] Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search Creating and [...]

  2. HumanJHawkins Says:

    What about finding plurals, but not other inflections. Does something like FORMSOF(PLURAL, light) exist? (I want to match “dog” with “dogs”, but not “run” with “ran”.

  3. arcanecode Says:

    Human – Sorry, nothing like that in FTS. Only suggestion I have is to use OR, and append an S to the string you are passing in. True, for some tenses it fail (goose vs geese) but would catch most of what you need.

  4. Presenting SQL Server 2005 2008 Full Text Searching at Alabama Code Camp « Arcane Code Says:

    [...] Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search Creating and [...]

  5. SQL Server Full Text Searching at the Atlanta Code Camp « Arcane Code Says:

    [...] Exploring SQL Servers FullTextCatalogProperty Function Using the ObjectPropertyEx Function Using FORMSOF in SQL Server Full Text Searching Creating Custom Thesaurus Entries in SQL Server 2005 and 2008 Full Text Search Creating and [...]

  6. Ed Graham Says:

    That is an excellent summary of the difference between CONTAINS and FREETEXT; I have looked all over the MSDN site for one and not found it, so thank you!

    P.S. Mild typo in a few places in para 4 (below the code sample) — “fulltext” for (presumably) “freetext”.

  7. Ed Graham Says:

    Note to HumanJHawkins: I had exactly this question myself, and used the suggested solution but appending “es” if the word ends in an “s” and a plain “s” otherwise. Obviously, sometimes this will generate nonsense words (e.g. “studieses”) but these won’t be contained in your document and hence won’t produce unwanted hits.

  8. PVilla Says:

    Great summary!! Clear and Concise. The last paragraph where the differences between FreeText and Contains are outlined, FreeText is mentioned as FullText. Can you make the correction so that readers won’t be misguided. Thank You!!


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 103 other followers

%d bloggers like this: