SQL Server全文搜索引导通配符 [英] SQL Server Full Text Search Leading Wildcard

问题描述

在看完问题，并做我自己的研究，似乎你不能有一个领先的通配符，而使用全文搜索。

所以在最简单的例子，如果我有一个如下表1列的表：

TABLE1

硬币

coinage

undercoin

从TABLE1中选择COLUMN1，其中COLUMN1 LIKE'％coin％'
会得到我想要的结果。

如何在列上启用全文搜索的情况下获得完全相同的结果？

以下两个查询返回完全相同的数据，这不完全是我想要的。

  SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS（COLUMN1，''coin *''）
 
 SELECT TABLE COLUMN1 FROM TABLE1 WHERE CONTAINS（COLUMN1，'* coin *'）
  

解决方案

全文搜索可以找到单词或单词的词组。因此，它在欠币的任何地方都找不到硬币这个词。你所寻找的是使用全文搜索的能力搜索后缀，而且它本身不会这样做。有一些hacky的解决方法，比如创建一个反向索引和在nioc上搜索。

After taking a look at this SO question and doing my own research, it appears that you cannot have a leading wildcard while using full text search.

So in the most simple example, if I have a Table with 1 column like below:

TABLE1

coin
coinage
undercoin

select COLUMN1 from TABLE1 where COLUMN1 LIKE '%coin%' Would get me the results I want.

How can I get the exact same results with FULL TEXT SEARCH enabled on the column?

The following two queries return the exact same data, which is not exactly what I want.

SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"coin*"')

SELECT COLUMN1 FROM TABLE1 WHERE CONTAINS(COLUMN1, '"*coin*"')

解决方案

Full text search works on finding words or stems of words. Thus, it does not find the word "coin" anywhere in "undercoin". What you seek is the ability search suffixes using full text searches and it does not do this natively. There are some hacky workarounds like creating a reverse index and searching on "nioc".

这篇关于SQL Server全文搜索引导通配符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！