CONTAINS showing results when shouldn't

Question

CONTAINS showing results when shouldn't

lleemon13

Ten Centuries

Points: 1063
More actions
January 24, 2012 at 2:20 pm

#255356

I have the following query that returns 1 record.
select * from Resume
where Resume_ID = 4187300
AND CONTAINS(Resume_Desc, '"Series 7"')
That would be great but if I do a search of the 'Resume_Desc' field I do not have a phrase that is 'Series 7'.
So I found another phrase without a numeric value and then removed one character 'n' from Centers.
select * from Resume
where Resume_ID = 4187300
AND CONTAINS(Resume_Desc, '"AAMCO Ceters"')
The result is as expected and no results show up.
Any ideas why my first query is showing the result when it shouldn't?
Thanks for any advice.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Ben Teraberry SSChampion Points: 11845 More actions · Answer 1

Very good question.

Something important to understand about SQL Server's full text indexing is that not every word gets indexed. There are words that are intentionally filtered out because they generally waste space and aren't used for real searching. In SQL 2005, these were referred to as noise words, which were contained in text files for each language in the SQL Server install directory. This concept was replaced in SQL 2008 with stoplists, which basically perform the same function but are contained within the database itself instead of external files. The default system stoplist is in the resource database.

Before going further, we should put two and two together and recognize that the relevant point is that '7' is, by default, listed as a noise word in 2005 and in the stoplist for 2008. All single digits are intentionally not indexed. So, your query basically completely ignores the '7' as it's not part of the full text index. You could replace '7' with any other single digit and you would get the same results.

If we want to change that, and include these single digits in our index, we can do that. First we want to create a new stoplist based on the default system one so we have something we can edit. In my example, I'm working in a database named Dev with a single table named dbo.FTS_Test.

use Dev

go

create fulltext stoplist Dev_FT_Stoplist from system stoplist;

go

Now one thing that might be interesting is to take a look and see what the stopwords are ...

select sw.*

from sys.fulltext_stopwords as sw

join sys.fulltext_stoplists as sl

on sl.stoplist_id = sw.stoplist_id

where sw.language_id = 1033

and sl.name = 'Dev_FT_Stoplist'

I just want to stick to English (thus the 1033) ... obviously that can be changed as needed. So we can see all the single digits in there and we want to get rid of them: