Full Text Search Noise Words Problem in Application
Posted on 2005-03-07
I'm developing an ASP.NET application (in C#) that uses MS SQL Server. I am using Full Text Searches to do the searching within the application. Eveything works fine, but the problem is that when a user enters a word that appears in SQL's noise words file, SQL returns an error stating that a search was submitted that contained only a noise word:
Execution of a full-text operation failed. A clause of the query contained only ignored words.
I’ve looked this up online, and apparently many people encounter this problem. The solutions I’ve seen online suggest:
a) Removing the list of noise words from the noise.eng file.
b) Filtering these words out client side (any time before they get to the DB).
Option A won’t work because there are several other applications (including Commerce Server) that use the same database server, so, I can't just change the noise.eng file. Besides, the noise words are supposed to be filtered out, so ideally it would be good to keep the noise words defined but just have them be filtered.
Option B could work, but it would require building a mirror of the noise.eng file, and filtering every single noise word against every single search term that is entered (which means breaking each of the 40 multi-word search terms entered on the advanced search page out into arrays of individual words, and comparing each word versus every single noise word). That seems like it would be a lot of work to code, and more importantly, it seems like it would have a terrible impact on performance.
I could just trap the error that SQL returns, but the problem is that the error does not indicate which search term was invalid, so I can't provide meaningful feedback to the user.
Any help would be greatly appreciated. Thanks.