Having trouble using 'AND' in CONTAINSTABLE SQL SERVER FULL TEXT SEARCH

Question

I've been using FULL-TEXT for awhile but I cannot seem to get the most relevant results sometimes.

If I have an field with something like An Overview of Pain Medicine 5/12/2006 and a user types An Overview 5/12/2006

So we create a search like:

"An" AND "Overview" AND "5/12/2006" - 0 results (bad)

"Overview" AND "5/12/2006" - 1 result (good)

The CONTAINSTABLE portion of my query:

FROM         ce_Activity A
INNER JOIN
    CONTAINSTABLE(View_Activities,(Searchable), @Search) AS KeyTbl ON A.ActivityID = KeyTbl.[KEY]

"Searchable" is a field contains the activity title, and start date(converted to string) in one field so it's all search friendly.

Why would this happen?

[UPDATE]

Okay I just tested the NOISE word theory. I used "Pain" AND "Overview" AND "5/12/2006" and it works fine.

But if I add "of" it fails. 'Of' and 'An' must be noise words.

Now the question is, how do I make this just IGNORE the words, instead of removing it from the result if a noise word exists?

Any tips?

http://stackoverflow.com/questions/1042/why-doesnt-sql-full-text-indexing-return-results-for-words-containing http://social.msdn.microsoft.com/Forums/en-US/searchserverdevelopersandcustomization/thread/0e6822e2-abf7-473c-8dea-d29499e55477 — Wills, Apr 27 '10 at 19:28
https://technet.microsoft.com/en-us/library/ms187914(v=sql.110).aspx — Jodrell, Feb 04 '15 at 14:03

score 3 · Answer 1 · answered Apr 27 '10 at 18:10

3

Perhaps your current word breaker ignores "an" as noise.

answered Apr 27 '10 at 18:10

Remus Rusanu

If that were the case it would still load a record because "Overview" AND "5/12/2006" WORKS. – Joshua Apr 27 '10 at 18:54
Okay I just tested this theory. I used "Pain" AND "Overview" AND "5/12/2006" and it works fine. But if I add "of" it fails. 'Of' and 'An' must be noise words. Now the question is, how do I make this just IGNORE the words, instead of removing it from the result if a noise word exists? Any tips? – Joshua Apr 27 '10 at 19:02
The noise words are controlled through stopwords and stoplists in SQL 2008, see http://technet.microsoft.com/en-us/library/ms142551.aspx – Remus Rusanu Apr 27 '10 at 22:07
2

You have the option to either turn on the `transform noise words` option, see http://technet.microsoft.com/en-us/library/ms187914.aspx, which will raise an error if your CONTAINS depends on stopwords. OR you can parse the expression in the client, compare with `sys.fulltext_stopwords` list and modify the expression to remove known stopwords. No free lunch, I'm afraid. – Remus Rusanu Apr 27 '10 at 22:10

1 Answers1