你如何实现全文搜索在SQL Server中的多个列？ [英] How do you implement a fulltext search over multiple columns in sql server?

问题描述

我试图在创建视图的两列上实现全文搜索：VendorName，ProductName。我有全文索引等工作，但实际的查询是什么导致我的一些问题。

我希望用户能够使用一些标准的搜索约定，AND OR NOT和用（）分组的术语，这很好，但我想在两者上应用搜索例如，如果我要运行一个查询，例如：

  SELECT * FROM vw_Search 
 WHERE CONTAINS（ （VendorName，ProductName），Apple and iTunes）
  

它似乎是将查询应用于每个列，即检查两个条款的供应商名称，然后检查两个不匹配的条款的产品名称，除非供应商被称为Apple iTunes。

如果我将查询更改为：

  SELECT * FROM vw_Search 
 WHERE CONTAINS（VendorName，Apple or iTunes）
 AND CONTAINS（ProductName，Apple or iTunes）
  

然后它可以工作但打破其他搜索查询（例如搜索苹果），并且从编写查询的用户开始，它没有多大意义，因为它们可能写入的内容是AND，但它需要一个或多个工作。

我想要的是，如果两个搜索字词之间有效，它会返回匹配所有名为apple的供应商与产品名称itunes for例如。

我应该在视图中创建一个单独的字段，用于连接供应商字段和产品字段，并在该新字段上执行第一个查询，或者是否存在某些我错过的内容除此之外，任何人都可以知道现有的查询验证方法吗？

解决方案

在早期版本的中SQL Server ，查询匹配多个列。

然而，这被认为是一个 bug 。为了匹配多个列，您应该连接他们在计算列中，并在该列上创建索引。

I am trying to implement a fulltext search on two columns which I created a view for: VendorName, ProductName. I have the full text index etc working but the actual query is what is causing some issues for me.

I want users to be able to use some standard search conventions, AND OR NOT and grouping of terms by () which is fine but I want to apply the search over both the columns so for example if I were to run a query such as:

SELECT * FROM vw_Search 
WHERE CONTAINS((VendorName, ProductName), "Apple AND iTunes")

It seems to be applying the query to each column individually i.e. checking vendor name for both terms and then checking product name for both terms which wont match unless the vendor was called "Apple iTunes".

If I change the query to :

SELECT * FROM vw_Search 
WHERE CONTAINS(VendorName, "Apple OR iTunes") 
AND CONTAINS(ProductName, "Apple OR iTunes")

then it works but breaks other search queries (such as searching for just apple) and from user writing the query it doesn't make much sense as what they are likely to write is AND but it requires an OR to work.

What I want is it to return if between the two the search term was valid so it would match all vendors named apple with a product name itunes for example.

Should I create a separate field in the view that concatenates the Vendor and Product fields and performs the first query on that new field or is there something I am missing out?

Aside from that would anyone know of an existing method of validating the queries?

解决方案

In earlier versions of SQL Server, the queries matched across multiple columns.

However, this was considered a bug.

To match across multiple columns, you should concatenate them in a computed column and create an index over that column.

这篇关于你如何实现全文搜索在SQL Server中的多个列？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！