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?
You specify them in parentheses; FREETEXTTABLE(tablename, (col1,col2,col3), 'expr')
or use an asterisk to seach all columns in the index.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With