SQL Server ContainsTable不返回带有术语"inn"的结果 [英] SQL Server ContainsTable not returning result with term "inn"

问题描述

我有一个名为hotel的表，其中包含以下信息:

I have a table called hotel with the following information:

• Hotel_Id:2950
• Hotel_Name:在公园客栈
• Hotel_Number:01234567
• Hotel_TypeId:1

• Hotel_Id: 2950
• Hotel_Name: Inn on the Park
• Hotel_Number: 01234567
• Hotel_TypeId: 1

我需要能够搜索名称列包含某些术语的记录.

I need to be able to search for records where the name column contains certain terms.

搜索是:

select * 
from ContainsTable(hotel, Hotel_Name, '"Inn on Park"')

我没有结果，但是如果我搜索:

I get no results but if I search:

select * 
from ContainsTable(hotel, Hotel_Name, '"In on Park"')

我知道

Key: 2950
Rank: 176

我发现客栈"一词存在一些问题，但是如果我搜索:

I figured there was some issue with the term "inn" but if I search for:

select * 
from ContainsTable(hotel, Hotel_Name, '"Inn"')

我得到相同的密钥:2950，排名:176结果.

I get back the same key: 2950, Rank: 176 result.

"inn"是引起此问题的关键字吗?

Is "inn" a keyword that is causing this problem?

推荐答案

这是我的理论.

您的酒店名称Inn on the Park将是这样的索引:

Your hotel name, Inn on the Park, would be index like this:

pos   word
1     Inn
2     (noise)
3     (noise)
4     Park

上的

和 the 是停止/杂音单词，并且不存储在索引中(但请注意，该位置仍在存储中).

on and the are stop/noise words, and not stored in the index (but notice that the position is still stored).

您正在按完整字符串搜索.考虑到干扰词，这将意味着:

You're searching by a full string. Taking in to account the noise words this would mean:

Query 1: "Inn on Park" -> "Inn (noise) Park"
Query 2: "In on Park" -> "(noise) (noise) Park"

您的索引字符串(酒店名称)为Inn (noise) (noise) Park，因此这将是第二个查询的部分匹配，但第一个查询没有匹配.

Your indexed string (hotel name) is Inn (noise) (noise) Park, so this would be a partial match on the second query, but no match on the first.

这可以通过例如搜索Inn 1 1 Park进行测试.这将返回结果.但是Inn 1 Park或1 Inn 1 Park不会(位置不对应).

This can be tested by for example searching for Inn 1 1 Park. This will return a result. But Inn 1 Park or 1 Inn 1 Park will not (positions does not correspond).

要修复"此问题，您可以使用其他运算符，例如AND，OR或NEAR:

To "fix" this you could use different operators, like AND, OR or NEAR:

"Inn" AND "Park"
"Inn*"
"Inn" NEAR "Park" 

这是两个屏幕截图，显示了您的主要查询结果.请注意，第二个将如何仅搜索" Park "或"噪音噪声Park "(其中任何一个都会返回结果):

Here are two screenshots, showing the results of your main queries. Notice how the second will only search for "Park", or "noise noise Park" (any of those would return results):

这篇关于SQL Server ContainsTable不返回带有术语"inn"的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！