搜索字符串中有任何噪音词时，SQL全文搜索不会返回任何结果 [英] SQL Full-Text-Search returns no results when any noise words are in search string

问题描述

我有一个asp.net网页，用于使用FTS在我们的数据库中搜索公司名称。他们在搜索框中输入公司名称并提交。

首先，我修剪任何前导或尾随空格，然后用NEAR替换每个内部空间。

所以搜索：

 左手扒手协会
  

提供为：

 NEAR Association NEAR of Nearly Lefthanded NEAR Picklepickers
  

在搜索结果网格中使用WHERE子句：

  WHERE CONTAINS（CompanyName，@Search）
  

这个方法可以很好地工作，直到用户搜索中包含噪音词（，，是和等等）。发生这种情况时，SQL Server不返回任何搜索结果。在上述搜索的情况下，即使数据库中存在具有确切标题的公司，of字也会中断搜索。

我该怎么做才能克服这个问题？

您的想法和建议非常感谢！

解决方案

尝试使用如下所述的变换噪声字选项： http ：//technet.microsoft.com/en-us/library/ms187914（SQL.90）.aspx

  sp_configure'show advanced options'，1 
 RECONFIGURE 
 GO 
 sp_configure'变换噪音字'，1 
 RECONFIGURE 
 GO 
  

I have an asp.net web page used to search for company names in our database using FTS. They enter the company name in a search box and submit.

First I trim any leading or trailing spaces, then i replace each internal space with " NEAR ".

So a search for:

"The Association of Lefthanded Picklepickers"

Is fed as:

"The NEAR Association NEAR of NEAR Lefthanded NEAR Picklepickers"

Into the following WHERE clause used by the search results grid:

WHERE CONTAINS(CompanyName, @Search)

This method works perfectly until a noise word (the, is, and, to, etc.) is part of users search. When this happens the SQL server returns no search results. In the case of the above search, the word "of" breaks the search even if a company with the exact title exists in the database.

What should I do to overcome this problem?

Your thoughts and advice are greatly appreciated!

解决方案

Try using the transform noise words option as described here: http://technet.microsoft.com/en-us/library/ms187914(SQL.90).aspx

sp_configure 'show advanced options', 1
RECONFIGURE
GO
sp_configure 'transform noise words', 1
RECONFIGURE
GO

这篇关于搜索字符串中有任何噪音词时，SQL全文搜索不会返回任何结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！