February 7, 2006 at 10:02 am
Hi,
I wonder if anyone can help me on this one.
I'm just getting to grips with 2005 and trying out the Full Text searching.
Is there any way that i can weight columns NOT search terms but columns in the index.
ie if i searched for "Harry Potter" and it appeared in a title field instead of just a book description field I would want that result to ranked higher that one where "Harry Potter" was contained in just the book description field. That means that I would like to weight the Title field higher than the Book Description field.
Currently I use Imceda Turbo SQL, and you can do it using this software. I would just like to know if it's possible with SQL 2005.
Cheers
Zac.
February 8, 2006 at 9:25 am
Hi Zac,
Have you tried using the WEIGHT parameter on two columns? For example, using the Northwest database table Employees and two FT-enable columns (Notes and Title):
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.1) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as B
where
A. = e.EmployeeID and
B. = e.EmployeeID
The column "Title" is weighted higher than the column "Notes". Let me know if this is what you're looking for as I have other examples on how to do this using other methods as well.
Thanks,
John
SQL 2005 Full Text Search Blog
John T. Kane
February 8, 2006 at 9:40 am
Cheers John,
I'm trying to draw up some comparison of MS SQL 2005 FTS v SQL Turbo.
Using your suggestion, with multiple containstable, my query may have 5 - 6 fields.
Does this mean that the FTS is hit multiple times also ? e.g Once for every Containstable.
Using this method I could end up with something like this :-
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
containstable(Employees, Notes, 'ISABOUT (BA weight (.1) )', 10) as A,
containstable(Employees, Title, 'ISABOUT (Sales weight (.5) )', 15) as B,
containstable(Employees, Name, 'ISABOUT (Sales weight (.5) )', 15) as C,
containstable(Employees, Address1, 'ISABOUT (Sales weight (.5) )', 15) as D,
containstable(Employees, Town, 'ISABOUT (Sales weight (.5) )', 15) as E
where
A. = e.EmployeeID and
B. = e.EmployeeID and
C. = e.EmployeeID and
D. = e.EmployeeID and
E. = e.EmployeeID
Also does it matter if one of the containstable returns empty? i.e could'nt find the search text in the column.
Regards
Zac
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply