您如何在SQL Server中使用领先的通配符全文搜索？

How do you get leading wildcard full-text searches to work in SQL Server?

注意：我正在使用SQL的全文本搜索功能，CONTAINS子句以及所有-*是全文本的通配符，％仅用于LIKE子句。

我已经在多个地方阅读过，MS SQL不支持"前导通配符"搜索(例如，使用" * overflow "来匹配" stackoverflow ")。我正在考虑使用CLR函数添加正则表达式匹配，但是我很想知道人们可能还有其他解决方案。

更多信息：您只能在单词或短语的末尾添加星号。 -以及我的经验：匹配" myvalue "时，" my * "可以工作，但是"(星号)value "返回的匹配项却不匹配，例如：

1	SELECT * FROM TABLENAME WHERE CONTAINS(TextColumn, '"*searchterm"');

因此，我需要解决方法。我只在网站上的实际搜索页面上使用搜索-因此它的工作方式基本上与Google相同(在Joe Sixpack类型的用户看来)。并没有那么复杂，但是这种匹配确实不会失败。

仅适用于前导通配符的解决方法：

将文本反转存储在其他字段(或实体化视图)中
在此列上创建全文本索引
使用*

查找反向文本

1
2
3
SELECT *
FROM TABLENAME
WHERE CONTAINS(TextColumnREV, '"mrethcraes*"');

当然，有很多缺点，只是为了快速解决......

更不用说CONTAINSTABLE ...

前导通配符的问题：无法对其进行索引，因此您正在执行全表扫描。

可以在单词或短语的末尾使用通配符" * "(前缀搜索)。

例如，此查询将找到所有" datab "，" database "，" databases " ...

1	SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"datab*"')

但是，不幸的是，无法使用前导通配符进行搜索。

例如，此查询将找不到" database "

1	SELECT * FROM SomeTable WHERE CONTAINS(ColumnName, '"*abase"')

为了使该线程更清晰，从我在2008 R2上进行的测试来看，Franjo在上面是正确的。在处理全文搜索时，至少在使用CONTAINS短语时，不能使用前导，只能使用尾随功能。 *是通配符，不是全文中的％。

有人建议忽略*。似乎并非如此，我的结果似乎表明尾随*功能确实起作用。我认为引擎会忽略前导*。

但是，我的附加问题是，带有后缀*的相同查询使用带通配符的全文本在2005(20秒)上相对较快地工作，而将数据库迁移到2008 R2后减慢了12分钟。似乎至少有一个用户有类似的结果，他在论坛上发了一条帖子，我添加到了该帖子中……FREETEXT仍然可以快速运行，但是随着" 2008"在CONTAINS中的尾随*的方式，某些"似乎"已经发生了变化。他们在升级顾问中发出各种警告，提示他们"已改进"全文，因此您的代码可能会中断，但不幸的是，它们并未向您提供有关某些已弃用代码等的任何具体警告。...只是免责声明已更改使用，后果自负。

http://social.msdn.microsoft.com/Forums/ar-SA/sqlsearch/thread/7e45b7e4-2061-4c89-af68-febd668f346c

也许，这是与这些问题最接近的MS命中... http://msdn.microsoft.com/zh-cn/library/ms143709.aspx

需要牢记的一件事是，与其他通配符用法相比，领先的通配符查询具有显着的性能优势。

注意：这是我在修订版#2中引入CONTAINS关键字之前针对问题的原始版本1提交的答案。实际上仍然是准确的。

SQL Server中的通配符是%符号，它可以正常工作，以开头，结尾或其他形式运行。

也就是说，如果您要进行任何形式的认真全文搜索，那么我将考虑利用全文索引功能。使用%和_通配符将导致数据库性能严重下降。

作为存储过程中的参数，您可以将其用作：

1
2
3
4
5
6
7
8
9
10
11
12

ALTER procedure [dbo].[uspLkp_DrugProductSelectAllByName]
(
@PROPRIETARY_NAME varchar(10)
)
as
set nocount on
declare @PROPRIETARY_NAME2 varchar(10) = '"' + @PROPRIETARY_NAME + '*"'

select ldp.*, lkp.DRUG_PKG_ID
from Lkp_DrugProduct ldp
left outer join Lkp_DrugPackage lkp on ldp.DRUG_PROD_ID = lkp.DRUG_PROD_ID
where contains(ldp.PROPRIETARY_NAME, @PROPRIETARY_NAME2)

仅供参考，Google不会进行任何子字符串搜索或截断，无论是右还是左。它们具有通配符*，可以在短语中找到未知词，但不能找到词。

Google与大多数全文搜索引擎一起，根据单词的字母顺序设置了反向索引，并提供了指向其原始文档的链接。二进制搜索的速度很快，即使对于巨大的索引也是如此。但是在这种情况下，实际上很难进行左截断，因为它失去了索引的优势。

如果您有权访问全文本搜索引擎的单词列表，则可以在此列表上执行"喜欢"搜索，并将数据库与找到的单词进行匹配，例如具有以下单词的表\\'words \\'：

1
2
3
4
5
6

pie
applepie
spies
cherrypie
dog
cat

要将此数据库中所有包含\\'pie \\'的单词与fts表\\'full_text \\'和字段\\'text \\'匹配：

1
2
3
4
5
6
7
8
9

to-match <- SELECT word FROM words WHERE word LIKE '%pie%'
matcher =""
a =""
foreach(m, to-match) {
matcher += a
matcher += m
a =" OR"
}
SELECT text FROM full_text WHERE text MATCH matcher

也许以下链接将为使用通配符提供最终答案：执行FTS通配符搜索。

请注意以下段落："但是，如果指定" Chain"或" Chain"，则不会获得预期的结果。星号将被视为普通的标点符号而不是通配符。"铅>

在全文搜索方面，对于我的金钱而言，Lucene胜过一切。有可用的.Net端口与Java版本创建的索引兼容。

需要做一些工作，您必须创建/维护索引，但是搜索速度非常快，并且您可以创建各种有趣的查询。甚至索引速度也非常好-我们每天只需要完全重建一次索引，不必担心会更新它们。

例如，此搜索功能由Lucene.Net提供支持。

从SQL Server联机丛书中：

To write full-text queries in
Microsoft SQL Server 2005, you must
learn how to use the CONTAINS and
FREETEXT Transact-SQL predicates, and
the CONTAINSTABLE and FREETEXTTABLE
rowset-valued functions.

这意味着上面用％和_编写的所有查询都不是有效的全文查询。

以下是调用CONTAINSTABLE函数时查询的示例。

SELECT RANK , * FROM TableName ,
CONTAINSTABLE (TableName, *, '
"*WildCard" ') searchTable WHERE
[KEY] = TableName.pk ORDER BY
searchTable.RANK DESC

为了使CONTAINSTABLE函数知道我正在使用通配符搜索，必须将其用双引号引起来。我可以在开头或结尾使用通配符*。为CONTAINSTABLE函数构建搜索字符串时，您可以执行许多其他操作。您可以在另一个词附近搜索一个词，搜索变形词(drive =驱动，驱动，驱动和从动)，并搜索另一个词的同义词(金属可以具有铝和钢之类的同义词)。

我刚刚创建了一个表，在该表上放置了全文索引，并进行了几次测试搜索，没有问题，因此通配符搜索可以按预期进行。

[更新]

我看到您已经更新了问题，并且知道您需要使用其中一个功能。

您仍然可以在开头使用通配符进行搜索，但是如果单词不是通配符后的完整单词，则必须在末尾添加另一个通配符。

1
2
3

Example: "*ildcar" will look for a single word as long as it ends with"ildcar".

Example: "*ildcar*" will look for a single word with"ildcar" in the middle, which means it will match"wildcard". [Just noticed that Markdown removed the wildcard characters from the beginning and ending of my quoted string here.]

[更新#2]

戴夫·沃德(Dave Ward)-将通配符与其中一个功能一起使用应该不会引起很大的反响。如果我仅使用" * "创建一个搜索字符串，它将不会返回所有行，在我的测试案例中，它将返回0条记录。

％匹配任意数量的字符
_匹配单个字符

我从未使用过全文索引，但是您只需使用内置的T-SQL字符串函数就可以完成相当复杂且快速的搜索查询。