CONTAINSTABLE query not recognising small words

Question

I'm using CONTAINSTABLE to search two table columns. Once the search contains small words like 'the' 'for' 'a' the search returns no results even when they are actually present in the column.

Quick example. Column being searched contains the text. 'System needs to be upgraded'

Following SQL returns 0 rows

SELECT     * FROM Incident WHERE (TicketNumber IN (
SELECT TicketNumber FROM         [Action] FT_TBL INNER JOIN
CONTAINSTABLE(Action, Text, '"system" AND "needs" AND "to" AND "upgraded" AND NOT "Search Summary"') KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
UNION
SELECT TicketNumber FROM [Incident] FT_TBL INNER JOIN
CONTAINSTABLE(Incident, Subject, '"system" AND "needs" AND "to" AND "upgraded"') AS KEY_TBL ON FT_TBL.TicketNumber = KEY_TBL.[KEY]))

Once 'to' is omitted it works fine:

SELECT     * FROM Incident WHERE (TicketNumber IN (
SELECT TicketNumber FROM         [Action] FT_TBL INNER JOIN
CONTAINSTABLE(Action, Text, '"system" AND "needs" AND "upgraded" AND NOT "Search Summary"') KEY_TBL ON FT_TBL.ID = KEY_TBL.[KEY]
UNION
SELECT TicketNumber FROM [Incident] FT_TBL INNER JOIN
CONTAINSTABLE(Incident, Subject, '"system" AND "needs" AND "upgraded"') AS KEY_TBL ON FT_TBL.TicketNumber = KEY_TBL.[KEY]))

How can CONTAINSTABLE be used with these smaller words, or should they be left out altogether? If those smaller words are actually meaningful in the search, how can they be included in the search?

https://technet.microsoft.com/en-us/library/ms187914(v=sql.110).aspx — Jodrell, Feb 04 '15 at 14:05

score 3 · Accepted Answer · answered Sep 29 '10 at 13:22

3

These are not "small words", these are common words that are on the stop list. They are ignored.

See Stopwords and Stoplists on BOL.

answered Sep 29 '10 at 13:22

GSerg

76,472
17
159
346

Thanks. I had an inkling that was the case, i was googling "CONTAINSTABLE forbidden words" aswell as "blocked words". Thanks for the info. – user48408 Sep 29 '10 at 16:38
To show these words: `SELECT * FROM sys.fulltext_system_stopwords` – Dherik Oct 05 '18 at 17:38

score 1 · Answer 2 · edited May 23 '17 at 12:11

1

Please see the end of my answer on creating and engaging custom Full-text Stoplist starting from empty one. I checked and with empty one your query works with "to", "be", "the", "for", etc.

Update:

SQL Server 2005 should have MSSQL\FTData\noiseENG.txt.
I'd reccommend to view answers to question "Noise Words in Sql Server 2005 Full Text Search"

edited May 23 '17 at 12:11

Community

1
1

answered Sep 29 '10 at 18:25

Gennady Vanin Геннадий Ванин

10,136
11
75
106

In SSMS 2005 there is no "Full Text Stoplist" node. It looks like its a new edition in 08. We may end up upgrading to 2008 anyway in which case I will try this. Thanks – user48408 Sep 30 '10 at 15:38

CONTAINSTABLE query not recognising small words

2 Answers2