sql server - Full text search does not work if stop word is included even though stop word list is empty

Question

I would like to be able to search every word so I have cleared the stop word list. Than I have rebuilt the index. But unfortunately if I type in a search expression with stop word in it it still returns no row. If I leave out just the stop word I do get the results. E.g. "double wear stay in place" - no result, "double wear stay place" - I get the results that actually contain "in" as well.

Does anyone know why this can be? I am using SQL Server 2012 Express.

Thanks a lot!

question from:https://stackoverflow.com/questions/65600919/why-does-microsoft-sql-contains-not-work-for-the-word-said

Answer 1

Meanwhile I have managed to solve the issue. The problem was that I had my own stop list which was indeed empty but my fulltext catalog was associated not with my own stoplist but with the system one. Here are a couple of useful queries for solving stopword and full text search issues:

Query stopwords (does not give back the system ones!):

select * from sys.fulltext_stopwords

Query stop lists (does not give back the system list!):

select * from sys.fulltext_stoplists

Check what words are included in the catalog:

SELECT * FROM sys.dm_fts_index_keywords(DB_ID('dbname'), OBJECT_ID('tablename'))

Check the association:

select fulltext_catalog_id,stoplist_id, * from sys.fulltext_indexes;

Turn stoplist off:

ALTER FULLTEXT INDEX ON CremeSearchFT SET STOPLIST = OFF

I hope it helps someone. :)