Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server Full-Text-Search FREETEXTTABLE search multiple columns

Tags:

sql

tsql

I'm using the below query to return results from a table using Full-Text-Search. In SQL2000 it was only possible to search one or all columns in a table. Is it possible in SQL 2008?

I would like to search 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 I can see the FREETEXTTABLE does not accept more than one column?

like image 739
madlan Avatar asked Aug 07 '10 12:08

madlan


2 Answers

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

like image 122
Alex K. Avatar answered Sep 18 '22 16:09

Alex K.


From MSDN,

Returns a table of zero, one, or more rows for those columns containing character-based data types for values that match the meaning, 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 like a regular table name. Queries using FREETEXTTABLE specify freetext-type full-text queries that return a relevance ranking value (RANK) and 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 as well.

like image 38
Tobiasopdenbrouw Avatar answered Sep 20 '22 16:09

Tobiasopdenbrouw