SQL 2008: Turn off Stop Words for Full Text Search Query

Question

I'm having quite a bit of difficulty finding a good solution for this:

Let's say I have a table of "Company", with a column called "Name". I have a full-text catalog on this column. If a user searched for "Very Good Company", my query would be:

SELECT
    *
FROM
    Company
WHERE
    CONTAINS(Name, '"Very" AND "Good" AND "Company"')

The problem is in this example, the word "Very" shows up in the standard list of stopwords:

SELECT
    ssw.*
FROM
    sys.fulltext_system_stopwords ssw
WHERE
    ssw.language_id = 1033;

Resulting in the query returning with no rows, even though there is a row with the name "Very Good Company".

My question is, how would I go about turning the stopwords off for my query? Or how would I go about removing them entirely?

Or is there another way I should be going about this search?

John · Accepted Answer · 2011-12-08T19:48:58.147

48

In case anyone else stumbles upon this problem:

It looks like there is an option to do this in 2008; it wasn't apparent to me because the database was upgraded from 2005 where I don't believe this was an option.

The first thing you need to do is set the compatibility level up to 2008:

ALTER DATABASE [MyDatabase] SET COMPATIBILITY_LEVEL = 100

Then, when creating the full-text index through the wizard, there is a step that allows you to ignore stopwords for the index

edit: Here's the script to do it as well:

ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = OFF

edited Dec 08 '11 at 19:48

answered Dec 08 '11 at 19:21

John

17,163
16
65
83

14

Turning it back on is a bit hidden in BOL. You don't just set it to ON, but need to either set it to the name of one of your own stoplists or use `SYSTEM` to use the built-in one: `ALTER FULLTEXT INDEX ON MyTable SET STOPLIST = SYSTEM` – Rhumborl Nov 10 '14 at 12:52

score 17 · Answer 2 · answered May 04 '12 at 14:59

17

By default in SQL Server the stopwords are not ignored.

This is what you want to do:

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

REF: http://msdn.microsoft.com/en-us/library/ms187914%28v=sql.100%29.aspx

answered May 04 '12 at 14:59

Matt Whittingham

855
9
7

1

You don't want to turn off the stop word list like the other post. Performance becomes a problem with large data sets. – Matt Whittingham Jun 11 '13 at 20:19
It doesn't work with prefix match though - if you search for `"cat*" and "the*"` that is. – strider Jan 18 '17 at 21:21

score 1 · Answer 3 · answered Sep 16 '13 at 18:17

I was having this issue earlier today with the full text search.

151-663049 - returns result
151-66304 - no result
151-6630 - no result
151-663 - no result
151-66 - no result
151-6 - returns result
151 - returns result
151 returns result

But I read a post that says to get around the issue to append a * to the end of each search. http://social.msdn.microsoft.com/Forums/sqlserver/en-US/fae33a6b-7c7c-4c11-842c-ca5277ed824f/ms-sql-server-2008-r2-fulltext-search-problem

151-663049* - returns result
151-66304* - returns result
151-6630* - returns result
151-663* - returns result
151-66* - returns result
151-6* - returns result
151-* - returns result
151* - returns result

So in your parameter just append * to your searches and problem solved.

SQL 2008: Turn off Stop Words for Full Text Search Query

3 Answers3

Linked