CODEWITHSUNDEEP
sql-serversql-server-2005full-text-search
Iain
Iain

Reputation:

Full-Text Index Comparisons in SQL Server 2005 Express

How does one compare the text field of one record to all the other records in SQL server to return, for example, the top 5 most related records?

An example of the functionality I'm after are the various Related Posts plugins for Wordpress that produce a list of links to posts related to the post currently being viewed.

Cheers, Iain

Upvotes: 2

Views: 585

Answers (3)

Iain
Iain

Reputation:

Thanks for these responses. I'm familiar with the referenced functions, but I'm not sure they do what I need. For example:

SELECT P.id, 'Product' AS Type, FT.rank, C.url + '/' + P.url AS url, longTitle, shortTitle, P.description
FROM Products P
     INNER JOIN CONTAINSTABLE (Products, (longTitle, shortTitle), '"my text content"') AS FT ON P.id = FT.[key]
     LEFT JOIN Product_Categories PC ON P.id = PC.productID
     LEFT Join Categories C ON C.id = PC.categoryID
WHERE [primary] = 1
ORDER BY rank DESC

returns only rows with the exact phrase "my text content" - I need rows with only "text" to be returned, but at a lower rank. If I change the query as follows:

SELECT P.id, 'Product' AS Type, FT.rank, C.url + '/' + P.url AS url, longTitle, shortTitle, P.description
FROM Products P
     INNER JOIN CONTAINSTABLE (Products, (longTitle, shortTitle), '"my" or "text" or "content"') AS FT ON P.id = FT.[key]
     LEFT JOIN Product_Categories PC ON P.id = PC.productID
     LEFT Join Categories C ON C.id = PC.categoryID
WHERE [primary] = 1
ORDER BY rank DESC

I get more rows, but rows with all three words don't appear to rank clearly higher than rows with 1 of the words.

Any further thoughts?

Upvotes: 1

Coolcoder
Coolcoder

Reputation: 4036

You need to use CONTAINSTABLE , this returns a RANK column you can use to sort by. 

SELECT TOP 5 [Key] FROM CONTAINSTABLE ([YourFullText],'SomethingToSearch')
ORDER BY [RANK] DESC

Upvotes: 0

Brent Ozar
Brent Ozar

Reputation: 13274

Here you go, from the excellent Robert Cain:

http://arcanecode.com/2007/06/28/getting-started-with-sql-server-2005-full-text-searching-part-3-%E2%80%93-using-sql/

Upvotes: 0

Related Questions