SQL Server Full Text Search FREETEXTTABLE Multi Column Search

Question

I use the following query to return results from a table using full-text search. In SQL2000, only one or all of the columns in a table could be searched. Is this possible in SQL 2008?

I would like to look for two tables: “Problem and Solution” (both indexed and in the same table):

DECLARE @topRank int set @topRank=(SELECT MAX(RANK) FROM FREETEXTTABLE([Support_Calls], Problem, 'test', 1)) SELECT [ID] AS [Call No],Company_Name, Problem, Solution, CONVERT(VARCHAR(20),CAST((CAST(ftt.RANK as DECIMAL)/@topRank * 100) AS DECIMAL(13,0))) + '%' as Match FROM [Support_Calls] INNER JOIN FREETEXTTABLE([Support_Calls], Problem, 'test') as ftt ON ftt.[KEY]=[ID] ORDER BY ftt.RANK DESC; 

From what can I see that FREETEXTTABLE does not accept more than one column?

Answer 1

You indicate them in parentheses; FREETEXTTABLE(tablename, (col1,col2,col3), 'expr') or use an asterisk to search for all columns in the index.

Answer 2

From MSDN ,

Returns a table with zero, one or more rows for columns containing character-based data types for values ​​that match the value but not the exact wording of the text in the specified freetext_string. FREETEXTTABLE can only be referenced in the FROM clause of a SELECT statement, for example, the name of a regular table. Queries using FREETEXTTABLE specify full-text freetext queries that return a relevancy ranking value (RANK) and a full-text key (KEY) for each row.

They give the following syntax:

 FREETEXTTABLE (table , { column_name | (column_list) | * } ,'freetext_string' [ , LANGUAGE language_term ] [ ,top_n_by_rank ] ) 

So, yes, what Alex K. said

Answer 3

If you created FULLTEXT INDEX for different columns, you can simply use CONTAINS or FREETEXT to view one of them, all 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 you want to search. If you need results in one column, you will need to do UNION and do a search for each column that you want to find.

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