为什么SQL Server全文搜索不匹配数字? [英] Why is SQL Server full-text search not matching numbers?

问题描述

我正在使用SQL Server 2014 Express，并且在表上具有全文本索引设置.

全文索引仅索引单个列，在本示例中为foo.

表中有3行.全文索引列的3行中的值如下所示...

test 1
test 2
test 3 test 1

上面的每一行都是表中的新行，而该文本实际上就是全文索引列中的内容.因此，使用SQL Server的CONTAINS函数，如果执行以下查询，则会按预期将所有行返回为匹配项.

SELECT * FROM example WHERE CONTAINS(foo, 'test')

但是，如果我运行以下查询，我还将所有行作为匹配返回，这是我所不希望的.在以下查询中，我只希望将一行作为匹配项.

SELECT * FROM example WHERE CONTAINS(foo, '"test 3"')

最后，仅搜索"3"不会返回匹配的行，这也是我所没有想到的.我希望从以下查询中找到一个匹配的行，但不会得到任何结果.

SELECT * FROM example WHERE CONTAINS(foo, '3')

我已经阅读了CONTAINS和全文索引上的MSDN页面，但是我无法弄清楚这种现象.我一定做错了.

任何人都可以向我解释发生了什么以及如何执行我描述的搜索吗?

解决方案

虽然这可能不是 the 答案，但它解决了我原来的问题.我的全文索引正在使用系统停止列表.出于某种原因，某些个别数字(例如测试1"中的"1")会被跳过，或者停止列表会执行任何操作.

以下关于SO的问题和答案建议一起禁用停止列表.我这样做了，现在我的全文本搜索符合我的期望，但看起来像是全文本索引更大.

Lastly, simply searching for "3" returns no matching rows, which I also did not expect. I'd expect one matching row from the following query, but get none.

SELECT * FROM example WHERE CONTAINS(foo, '3')

I've read the MSDN pages on CONTAINS and full-text indexing, but I can't figure out this behavior. I must be doing something wrong.

Would anybody be able to explain to me what's happening and how to perform the searches I've described?

解决方案

While this may not be the answer, it solved my original question. My full-text index was using the system stop list. For whatever reason, certain individual numbers, such as "1" in "test 1", were being skipped or whatever the stop list does.

The following question and answer, here on SO, suggested disabling the stop list alltogether. I did this and now my full text searches match as I expected them to, at the expense of a larger full text index, it looks like.

Full text search does not work if stop word is included even though stop word list is empty

这篇关于为什么SQL Server全文搜索不匹配数字?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！