3

I'm fairly new to Full Text Index in SQL server. It has been working really well for me however, recently someone did an exact match search for "IT Manager" and the "IT" part of the search seems to be ignored.

e.g.

SELECT * FROM CONTAINSTABLE(vCandidateSearch, SearchText, '"it manager"')

and 

SELECT * FROM CONTAINSTABLE(vCandidateSearch, SearchText, '"manager"')

return the same results. What am I doing wrong?

beercohol
  • 2,577
  • 13
  • 26
Paul Johnson
  • 1,417
  • 1
  • 17
  • 26

2 Answers2

4

The problem is that the fulltext engine sees "it" as a "noise" - or stop - word, and ignores it.

Assuming you're using SQL 2008+, then see the documentation here on stoplists and stopwords: https://msdn.microsoft.com/en-us/library/ms142551(v=sql.100).aspx

These are lists containing various "filler" words (e.g. "a" "the" "it" etc) in various languages, that are generally not useful in fulltext searches and are ignored.

My experience is that these default lists are great for searching larger bodies of text, but often not so useful for things like product (or indeed job) titles that need to be more specific.

You can create your own stoplists containing (or not) whatever stopwords are appropriate for your particular need.

For a job title search it may well be appropriate to use no stopwords at all for that particular column. You can choose which stoplist (containing stopwords) is associated with a particular fulltext index when the index is created. You can create an empty list if need be, and use it in an index on one column only (although you would have to adjust your queries to take this into account).

In the unlikely event you're on SQL 2005 or below, it uses a much more primitive system of "noise words" that are just held in a text file: https://msdn.microsoft.com/en-us/library/ms142551(v=sql.90).aspx

beercohol
  • 2,577
  • 13
  • 26
  • Thanks for this. I will have to check that out. The search actually searches CVs (Resumes) of candidates its not a job listing site. We use SQL server 2014. – Paul Johnson Nov 18 '15 at 22:44
  • Thank you very much. Your advice about the stoplists was exactly what I needed... although I'm wondering if its a good idea to remove IT from IT now :) – Paul Johnson Nov 23 '15 at 16:55
  • No probs, glad to help! What I've found is that using an empty stoplist works well for things like titles that need quite an exact search. For larger bodies of text you normally would want to eliminate the noise words to keep the results relevant. – beercohol Nov 23 '15 at 17:12
1

"" doesn't mean an exact match. It just looks for that phrase in the text.

If I have a value The big red house

Example matches

"big red house"
"big"
"house"
"red house"

Example of a non match

"the big yellow"

If you need that only "The big red house" matches then you might be better off creating a non-clustered index on that column and using a regular = predicate

Vanlightly
  • 1,389
  • 8
  • 10
  • OK thanks. I can see I misunderstood what the quotes do anyway. Although it still works well for what I need. I can't just use = as it could be contained anywhere. The LIKE '%IT Manager%' would do the job but in most cases I want the features of full-text search. The search is coming from a keywords box. – Paul Johnson Nov 18 '15 at 22:40
  • This is not the correct answer. "it" is a noise word @beercohol +1 – tomjm Nov 08 '16 at 13:38