views:

51

answers:

2

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?

+1  A: 

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

See Stopwords and Stoplists on BOL.

GSerg
Thanks. I had an inkling that was the case, i was googling "CONTAINSTABLE forbidden words" aswell as "blocked words". Thanks for the info.
+1  A: 

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"

vgv8
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
Plz see my Update
vgv8