SQL Full-Text-Search returns no results when any noise words are in search string.

Question

I have an asp.net web page used to search for company names in our database using FTS. They enter the company name in a search box and submit.

First I trim any leading or trailing spaces, then i replace each internal space with " NEAR ".

So a search for:

"The Association of Lefthanded Picklepickers"

Is fed as:

"The NEAR Association NEAR of NEAR Lefthanded NEAR Picklepickers"

Into the following WHERE clause used by the search results grid:

WHERE CONTAINS(CompanyName, @Search)

This method works perfectly until a noise word (the, is, and, to, etc.) is part of users search. When this happens the SQL server returns no search results. In the case of the above search, the word "of" breaks the search even if a company with the exact title exists in the database.

What should I do to overcome this problem?

Your thoughts and advice are greatly appreciated!

Answer 1

Try using the transform noise words option as described here: http://technet.microsoft.com/en-us/library/ms187914(SQL.90).aspx

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'transform noise words', 1
RECONFIGURE
GO