需要有关Sql Server 2008和全文搜索的帮助

I've setup a FTS on a single field, in a single table.

我在单个表中的单个字段上设置了FTS。

Field: Name NVARHCHAR(350) NOT NULL

Now, when i search for the following

现在,当我搜索以下内容时

1 ave
10 ave

i don't get back the results i expect.

我没有收到我期望的结果。

Firstly, the search query 1 ave is transformed into "1*" AND "ave*". Now i run my CONTAINS(..) query...

首先,搜索查询1 ave被转换为“1 *”和“ave *”。现在我运行我的CONTAINS(..)查询...

SELECT FooId, Name
FROM [dbo].[Names] 
WHERE CONTAINS(Name, @SearchQuery)

Then, along with the correct results, i also get these incorrect results back...

然后,连同正确的结果,我也得到了这些不正确的结果......

2 Ave  (a couple of entries .. but they are all unique entires).

So, how did this get retrieved? there is no 1* in that piece of text? Its like .. the number is ignored?

那么,这是如何得到的?那段文字中没有1 *?它就像..数字被忽略了?

Also - and this is important - i've removed an reference to a stop list AND rebuilt the catalog.

此外 - 这很重要 - 我删除了对停止列表的引用并重建了目录。

Hmm. I'm so confused. anyone have any suggestions?

嗯。我很困惑。有人有什么建议吗?

2 个解决方案

#1

The "1" can occur anywhere within the full text search indexed column it doesn't have to be directly before (or even before) the "Ave", is there a 1 somewhere else in that row?

“1”可以出现在全文搜索索引列中的任何位置,它不必直接在“Ave”之前(或甚至之前),该行中的其他地方是否有1?

#2

Full text indexing will find derivitaves of words - like if you search for RUN, it might find RUNNING, RAN, RUN, etc.

全文索引将找到单词的派生词 - 如果您搜索RUN,它可能会找到RUNNING,RAN,RUN等。

I wonder if its deciding that 2 is near 1, and returning that as a near match. You should try switching your query to a CONTAINSTABLE query so that you can also evaluate the RANK to determine which of the answers is a closer match. You could then decide on a threshold and filter out any rows that don't meet your criteria as to how close of a match they are.

我想知道它是否决定2接近1,并将其作为近似匹配返回。您应该尝试将查询切换到CONTAINSTABLE查询,以便您还可以评估RANK以确定哪个答案更接近匹配。然后,您可以决定阈值并过滤掉任何不符合条件的行,以确定它们的匹配程度。

EDIT: Its not doing the inflection thinking 1 is near 2. I ran a test query on a sample table that looked like this...

编辑:它没有做拐点思考1接近2.我在样本表上运行了一个测试查询,看起来像这样......

PK          Name
1           1 ave
2           10 ave
3           2 ave
4           12 avenue
5           13 avenue
6           100 ave.
7           200 ave
8           210 avenue

Here's the query I ran...

这是我跑的查询......

select  *
from    Table_1
where   contains(name, '"1*" and "ave*"')

And here's the results I get...

这是我得到的结果......

PK          Name
2           10 ave
4           12 avenue
5           13 avenue
6           100 ave.

The interesting thing here is that the first record in the table isn't found. (I ran this on SQL 2008 Dev edition). Based on those results (where nothing starting with 2 was found) - I'd double-check your query. Maybe post the full text of your entire query, including where the search variable is being set.

这里有趣的是找不到表中的第一条记录。 (我在SQL 2008开发版上运行了这个)。基于这些结果(没有找到以2开头的内容) - 我会仔细检查您的查询。也许发布整个查询的全文,包括搜索变量的设置位置。

#1