Tell Me Sql Server Full-text Searcher Is Crazy, Not Me

i have some customers with a particular address that the user is searching for: 123 generic way There are 5 rows in the database that match: ResidentialAddress1 =================

Solution 1:

The message is telling you that "way" is a stopword, which means it's ignored and not indexed. That's why you can find "wayne" but not "way".

So, no, it's not crazy and neither are you. There's just a simple misunderstanding.

Solution 2:

You probably used the system stoplist when you created the FT index. The word way happens to be in there. You can see it with this query:

SELECT*FROM sys.fulltext_system_stopwords
WHERE stopword ='way'AND language_id =1033

You can turn off the stoplist or create a custom one, but a better solution would be to write the query properly; don't use multiple WHERE CONTAINS clauses, combine them into one. Otherwise SQL Server might not be able to use the FT index as effectively.

Your query should look like this instead:

SELECT ResidentialAddress1 FROM Patrons
WHERECONTAINS(Patrons.ResidentialAddress1, '"123*" AND "generic*" AND "way*"')

If you do it this way, the stop word simply gets ignored; it'll still return all of the same results it would have returned if you hadn't included the term way*.

---

Edit: Just noticed that you tagged this sql-server-2000, so the first query might not work. In SQL 2000, they are "noise words" and I believe that the configuration is global, you don't have individual stoplists. Nevertheless, you'll still get results if you write a single WHERE CONTAINS clause instead of several.

To edit the noise words in SQL Server 2000, you have to edit the language-specific file in the SQL Server FTDATA configuration folder. More details are here: SQL Server Full Text Search Noise Words and Thesaurus Configurations.

Solution 3:

Solution 1:

You want to try the Transform Noise Word option (SQL 2008).

Turning this off, should stop word removal.

example:

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

Edit 1:

Hopefully there may be something similar for older versions of MS SQL?

Solution 4:

http://www.codinghorror.com/blog/2008/11/stop-me-if-you-think-youve-seen-this-word-before.html

In that post, Jeff mentions you can turn them off.

Solution 5:

Perhaps it requires more than three alphabet characters. Try another three letter word, like gen*.