Configure FreeTextTable

Configure FreeTextTable

Post by Brian Binn » Wed, 21 Mar 2007 03:43:17


Hi,

I am trying to use freetext to return "best match" results from a database
with movie titles, and it gives me a few different results than what I would
like it to.

I should properly mention that I have just looked at freetext in a few days,
so bare with my limited knowledge.

I have a query like this:

SELECT TOP 10 tblTitles.nvcTitle FROM tblTitles,
FREETEXTTABLE (tblTitles, nvcTitle, 'Big Mommas House') AS KEY_TBL
WHERE
tblTitles.guid = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

And the query works, I am just not happy with the result, properly because
the word "Big" gives a higher rank when it's located in the string more than
once, the results I get is the following (ordered):

1. Dragnet (1951/ Digiview Entertainment), Vol. 3: The Big Crime / The Big
Shoplift / The Big Girl
2. World's Famous Detectives, Vol. 2: Dragnet: Big Crime / Big Pair / Big
Producer / Big Break / Big September Man / ...
3. Big Momma's House (Widescreen)
4. Bear In The Big Blue House: Storytelling With Bear
5. Big Momma's House 2
6. Big Momma's House
7. Bear In The Big Blue House: Sense-Sational!
8. Bear In The Big Blue House: Tidy Time With Bear
9. Big Momma's House
10. Bear In The Big Blue House: Sleepy Time With Bear And Friends
(Columbia/Tri-Star)

Notice that instead of finding "Big Momma's House" title(s), it ranks two
other titles higher because the word "Big" is in there many times. Next, it
ranks "Big Momma's House 2" higher than "Big Momma's House" although I would
say the first title should be more accurate.

Can I in any way configure my freetext to only rank a word once, and perhaps
re-configure something to improve it for my purpose, or is the freetext
engine fixed to it's default ranking method?

Regards,

Brian Binnerup
 
 
 

Configure FreeTextTable

Post by Hilary Cot » Wed, 21 Mar 2007 03:54:31

No, there is no way supported to currently do this. The feature you are
looking for is called hitcount and ships with other Microsoft Search
products.

I would try containstable as it is a more stricter match.

--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.yqcomputer.com/

Looking for a FAQ on Indexing Services/SQL FTS
http://www.yqcomputer.com/

 
 
 

Configure FreeTextTable

Post by Brian Binn » Wed, 21 Mar 2007 06:05:05

Hi Hilary,

Thank you for the quick reply.

It's unfortunate that this can't be done.

But, the containstable seem to allow only single word searches? I here have
multiple.

Would you split them up in some way then?

Regards,

Brian
 
 
 

Configure FreeTextTable

Post by Brian Binn » Wed, 21 Mar 2007 06:19:29

tried to construct the following:

SELECT TOP 10 tblTitles.nvcTitle, KEY_TBL.RANK FROM tblTitles,
CONTAINSTABLE (tblTitles, *, 'ISABOUT(Big WEIGHT (.9), Mommas WEIGHT (.8),
House WEIGHT (.7))') AS KEY_TBL
WHERE
tblTitles.guid = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

But, it suffers from the same problem:

1. Dragnet (1951/ Digiview Entertainment), Vol. 3: The Big Crime / The Big
Shoplift / The Big Girl 313
2. Big Momma's House 261

Again, the title with three "Big" words is listed first.

Can I in any way get it to only count a word once?

Regards,

Brian

"Hilary Cotter" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

Configure FreeTextTable

Post by Hilary Cot » Wed, 21 Mar 2007 08:56:53

rap your search phrase in double quotes. IE

SELECT TOP 10 tblTitles.nvcTitle, KEY_TBL.RANK FROM tblTitles,
CONTAINSTABLE (tblTitles, *, ' "Big momma''s house" ') AS KEY_TBL
WHERE
tblTitles.guid = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC



--
Hilary Cotter

Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com



"Brian Binnerup" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...