CONTAINSTABLE wildcard fails on phrase match?

问题:

I have a search query that takes a phrase typed in by the user and should return a result, here is an example:

SELECT rank,[KEY] FROM CONTAINSTABLE(tblproduct, (title,subtitle,publisher_name),  @search_text,15)

Where @search_text is

Set @search_text = '"my favourite word*"'

The phrase, as it stands, is an exact title match to a book in the database. However, the wildcard (*), makes it so that it doesn't return ANY results.

When I omit the wildcard, I get an exact phrase match, however, some other phrases, like "The Imperfectionists" returns no results with or without the wildcard in place.

Why is this? What can I check?

Currently, if no result-set is generated for the wildcard, I run the query without the wildcard to see if it returns anything. If that fails too, I run a normal "like" statement.

Obviously the like statement will take forever... and I don't want to use it on a 7.6 million-strong database for a search that's supposed to be done with fulltext...

Any help appreciated!

回答1:

Using JStead's suggestion, I looked at the output for your string. I also found this at MSDN:

When is a phrase, each word contained in the phrase is considered to be a separate prefix. Therefore, a query specifying a prefix term of "local wine*" matches any rows with the text of "local winery", "locally wined and dined", and so on.

So, the word My is considered a "Noise word" by default. Adding the * causes the parser to look for any words starting with My. However, when searching the index, the word My seems to already have been removed (remember, it's a noise word), so it doesn't find the match.

I'm thinking about running my search string through the sys.dm_fts_parser function, removing noise words, before adding the *.

回答2:

There is a sys function that tells you exactly how Microsoft is going to search the terms you put in. This will tell you all the words it is going to search for. Use the following

SELECT * FROM sys.dm_fts_parser (' "my favourite word*" ', 1033, 0, 0) 

For more information on this function http://msdn.microsoft.com/en-us/library/cc280463.aspx

Instead of using the * you will want to update your thesaurus to give you all the possible inflections of my favorite word.