weight results in a fulltext catalog search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

weight results in a fulltext catalog search

Author

Topic

rem1th0mas
Starting Member

2 Posts

Posted - 2007-07-03 : 11:03:41

Hi
I've got a table containing over 1 million books references.
my 'books' table has 4 nvarchar columns Title, Author, ISBN and Blurb and a auto increment indexed ID called 'BooksID'
A full text catalog has been build over those 4 columns and I'm using 'BooksID' as a Unique Index

At the moment my query looks like this:

SELECT BooksID,ISBN,Title,Author,Blurb
FROM Books b
WHERE
CONTAINS((b.ISBN,b.Title,b.Author,b.Blurb), @SearchQuery)

Imagine we're looking for "Harry Potter", the results I want first are the ones where "Harry Potter" appears in the books title, the books talking about the sorcerer (eg where the name appears in the blurb field) should come last

Is there a function somewhere to weight each column?

Many thanks for your help
RT

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-07-03 : 11:21:35

Try CONTAINSTABLE() function.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

rem1th0mas
Starting Member

2 Posts

Posted - 2007-07-03 : 12:24:07

Hi Harsh
Thanks for your reply,
Unfortunatelly FreetextTable() and CONTAINSTABLE() allow you to perform only a search on 1 single column..

Since i've posted my query, I've done a bit of work and I've got 'a' solution that i'm posting below for reference only.
(quite ugly, actually but i don't have better so far)

SELECT
b.ID,b.ISBN,b.Title,b.Author,b.Blurb, sum(rank1+rank2+rank3+rank4) as SearchScore
from
(
SELECT top 200
i.ID
,i.ISBN
,i.Title
,i.Author
,i.Blurb,
case when CONTAINS(i.ISBN,@SearchQuery) then 800 else 0 end as rank1,
case when CONTAINS(i.Title,@SearchQuery) then 500 else 0 end as rank2,
case when CONTAINS(i.Author,@SearchQuery) then 400 else 0 end as rank3,
case when CONTAINS(i.Blurb,@SearchQuery) then 100 else 0 end as rank4

FROM BooksImported i
where CONTAINS((i.ISBN,i.Title,i.Author,i.Blurb), @SearchQuery)
) B
group by b.ID,b.ISBN,b.Title,b.Author,b.Blurb
order by SearchScore desc

I decided to go with contains() instead of freetext()

I would greatly appreciate any feedbacks of how to improve this and increase its performance which are obviously quite poor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources