Sql Server Full Text Search Single Result Column Searched Across Multiple Columns

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

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

The columns are ProductName, ProductNumber, and 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"

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)

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

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:

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

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

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.

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

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.