搜索多个列的Sql Server全文搜索单个结果列

I am trying to implement an AutoComplete search box(like google) using SQL Server 2008 and Full Text Search.

我正在尝试使用SQL Server 2008和全文搜索实现自动完成搜索框（如谷歌）。

Say I have 3 columns that I want to search across and have created the proper indexes and what not.

假设我有3列我想搜索并创建了正确的索引，什么不是。

The columns are ProductName, ProductNumber, and Color...

列是ProductName，ProductNumber和Color ...

For the user input I want to search for possible matches across all three columns and suggest the proper search term.

对于用户输入，我想在所有三列中搜索可能的匹配，并建议正确的搜索词。

So say the user starts typing "Bl"

所以说用户开始输入“Bl”

id like to return a single column containtng results like "Black" "Blue" which come from the Color column and also any matches from the other two columns(like ProductNumber: BL2300)

id喜欢返回单个列containtng结果，如“Black”“Blue”，它来自Color列，还有来自其他两列的任何匹配（如ProductNumber：BL2300）

So basically I need to search across multiple columns and return a single column as the result. Is there a way to do this?

所以基本上我需要搜索多个列并返回一个列作为结果。有没有办法做到这一点？

2 个解决方案

#1

UPDATED follwoing comment of op If you created a FULLTEXT INDEX on different columns, then you can simple use CONTAINS or FREETEXT to look on one of them, all of them, or some of them. Like this:

更新的op的以下注释如果您在不同的列上创建了FULLTEXT INDEX，那么您可以简单地使用CONTAINS或FREETEXT来查看其中一个，所有这些或其中一些。喜欢这个：

SELECT *
FROM YourTable
WHERE CONTAINS(*, @SearchTerm);

If you want to look on all the columns that are included in the FULLTEXT INDEX. or:

如果要查看FULLTEXT INDEX中包含的所有列。要么：

SELECT *
FROM YourTable
WHERE CONTAINS((ProductName, ProductNumber, Color), @SearchTerm);

If you want to specify the columns that you want to search. If you need the results in one column, you are gonna have to do a UNION and do a search for every column you want to be searched.

如果要指定要搜索的列。如果您需要在一列中显示结果，则必须执行UNION并搜索要搜索的每个列。

    SELECT *
    FROM YourTable
    WHERE CONTAINS(ProductName, @SearchTerm)
    UNION
    SELECT *
    FROM YourTable
    WHERE CONTAINS(ProductNumber, @SearchTerm)
    UNION 
    SELECT *
    FROM YourTable
    WHERE CONTAINS(Color, @SearchTerm)

#2

If you do not need to associate the single columns, something like

如果您不需要关联单个列，例如

SELECT * FROM Table WHERE ProductName LIKE @SearchTerm + '%'
UNION
SELECT * FROM Table WHERE ProductNumber LIKE @SearchTerm + '%'
UNION
SELECT * FROM Table WHERE Color LIKE @SearchTerm + '%'

is a good point to start from.

是一个很好的起点。

#1