Can't use words like "Can", "of" in full text Search

Question

Hi,

I am using MsSQL server 2008, I am using CONTAINSTABLE with the search term "can", but the search query is not returning anything thought there are no stoplists attached to that catalog.

SELECT DISTINCT c.ID FROM City c INNER JOIN CONTAINSTABLE(City, (Name), '"can"') AS city_tbl1 ON city_tbl1.[KEY] = c.ID

To make sure that this kinda exists I ran this query

update City SET Name = 'can' WHERE ID = 34

no results where returned.

Please help.

Thanks in advance --Z

Answer 1

I'm not familiar with CONTAINSTABLE, but your use of can looks peculiar

Is there a reason you have used '"can"' in your first SQL, and not 'can'?

Answer 2

Hi,

SQL Server has a textfile with common words that are ignored in the searches. If you want to add / remove words look in the directory Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTDATA\

Hope that helps.

Answer 3

These words are so called "stopwords". They're ignored by the full text engine.

In mssql 2008 you can clear the stopwords-table by executing following code:

--Disable the stopwords-filter-- use yourDatabase;

create fulltext stoplist EmptyStopList;

alter fulltext index on yourFullTextTable set stoplist EmptyStopList;

If the above commands do not work, make sure your database compatibility level is set to 100 (this means it's a 2008 database).

--Make sure-- EXEC sp_dbcmptlevel youDatabase, 100;

Answer 4

Thank you for your reply. I figured it out after you mentioned the confiuration, so thanks a lot.

But the issue was that can is in the system stopwords that somehow controls the index, so in the config I enabled transform noise words.

sp_configure 'transform noise words', 1; GO RECONFIGURE;

that just made it work.

Thank so much for your reply. Cheers --Z