When performing full-text searches you usually want not just results, but a ranking indicating how close a match is to what you are looking for. In SQL Server 2005, ranks are accessed via ranking functions - FULLTEXT searches are ranked using function FULLTEXTTABLE() and CONTAINS searches are ranked using function CONTAINSTABLE(). Both of these functions are used the same way, and both accept search patterns, the same search patterns supported by the FULLTEXT and CONTAINS predicates themselves.
Here is an example:
SELECT f.rank, note_id, note_text
FROM productnotes,
FREETEXTTABLE(productnotes, note_text, 'rabbit food') f
WHERE productnotes.note_id=f.[key]
ORDER BY rank DESC;
This example performs a FREETEXT type search. Instead of filtering using the WHERE clause, the FREETEXTTABLE() function is used and given a search pattern instructing the full-text engine to match any rows that contain words meaning rabbit and food. FREETEXTTABLE() returns a table which is given an alias of "f" (so as to be able to refer to it in column selection and the join), this table contains a column named "key" which will contain the primary key value of the table that was indexed (productnotes in this example), and "rank" which is the rank value assigned. And finally, results here are sorted by rank descending, as the higher the rank the greater the match.
It is also possible to assign weight values to search patterns and words. The rankings assigned in the example used here assumed that all words were equally important and relevant. If this is not the case, and some words are more important than others, then the ISABOUT() function can be used to assign relative weights, and the full-text search engine will then use these values when determining rankings.
Love the post; I have the full text search up and running. I used your SQL but the rank on each of the results = '0'.
Any Ideas?
RF
Either you can rebuild the FTC using the following query or right clicking the FTC from SQL Server Management Studio
ALTER FULLTEXT CATALOG catalog_name
REBUILD
is there a rational explanation for which after some select statements, the rank returned by the full-text search engine is 0, knowing that just after the repopulation the rank is displayed correctly?
in other words, time and usage messes up the ranking....
thanks!
I am doing FREETEXT search in my Database.
I have indexed my table (Article_ArticleContent) and included some of the columns.
However, executing the query do not returns any records:
SELECT
cArticleTitle, cShortDescription
FROM
Article_ArticleContent
WHERE CONTAINS
(cArticleTitle,' "a*" ');
Although executing the following query, it returns me the correct number of records:
select cArticleTitle, cShortDescription
from dbo.Article_ArticleContent
where cArticleTitle LIKE 'a%'
Can you please let me know if I am missing something in Indexing or in the SELECT query.
Ankit