How to get exact matches & better ranked results using freetexttable?

I have a SQL Server 2000 table with a varchar column (these are part
numbers) that contains data like this:
145
145-SC
145-AB
145-2
145-8
The full text catalog also includes columns that hold part name and
part description. I'm using the following query to return results for
product searches:
select ftt.RANK,
products.ref,
products.partno,
products.partname,
products.refparent
from PRODUCTS
INNER JOIN
FREETEXTTABLE(products,*,'145-8') as ftt
ON
ftt.[KEY]=products.ref
order by ftt.rank desc
My results are:
rankrefpartnoname
278385414Aqua kit
278385514-SCDAqua kit
278385614-SCZAqua kit
278385714-UGAqua kit
183386819Standard T&L Kit
183386919-SCDStandard T&L Kit
183387019-SCZStandard T&L Kit
183387119-UBStandard T&L Kit
754121145BADGE, 7.00 X 7.00
754122145-8BADGE, 7.00 X 7.00
754123145-25BADGE, 7.00 X 7.00
754124145-SCDBADGE, 7.00 X 7.00
754125145-SCZBADGE, 7.00 X 7.00
754126145-UBBADGE, 7.00 X 7.00
How can I improve my query to return 145-8 (in this instance) as the
highest ranking result?
Thanks for your help!
Its hard to tell from looking at your data what data belongs in what
columns, for example in this row
rank ref partno name
75 4126 145-UB BADGE, 7.00 X 7.00
is rank=75, ref=4126, partno=145-UB BADGE and name= 7.00 X 7.00
or is it something else like this
rank=75, ref=4126, partno=145-UB and name=BADGE, 7.00 X 7.00
Did you remove all of the letters and number from your noise word list?
Also perhaps you should just search on partno.
Also I think contains will give you what you are looking for, but its hard
to tell as I don't really understand what your data looks like.
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
<jasolution@.gmail.com> wrote in message
news:1168698214.665262.131510@.m58g2000cwm.googlegr oups.com...
>I have a SQL Server 2000 table with a varchar column (these are part
> numbers) that contains data like this:
> 145
> 145-SC
> 145-AB
> 145-2
> 145-8
> The full text catalog also includes columns that hold part name and
> part description. I'm using the following query to return results for
> product searches:
> select ftt.RANK,
> products.ref,
> products.partno,
> products.partname,
> products.refparent
> from PRODUCTS
> INNER JOIN
> FREETEXTTABLE(products,*,'145-8') as ftt
> ON
> ftt.[KEY]=products.ref
> order by ftt.rank desc
> My results are:
> rank ref partno name
> 278 3854 14 Aqua kit
> 278 3855 14-SCD Aqua kit
> 278 3856 14-SCZ Aqua kit
> 278 3857 14-UG Aqua kit
> 183 3868 19 Standard T&L Kit
> 183 3869 19-SCD Standard T&L Kit
> 183 3870 19-SCZ Standard T&L Kit
> 183 3871 19-UB Standard T&L Kit
> 75 4121 145 BADGE, 7.00 X 7.00
> 75 4122 145-8 BADGE, 7.00 X 7.00
> 75 4123 145-25 BADGE, 7.00 X 7.00
> 75 4124 145-SCD BADGE, 7.00 X 7.00
> 75 4125 145-SCZ BADGE, 7.00 X 7.00
> 75 4126 145-UB BADGE, 7.00 X 7.00
> How can I improve my query to return 145-8 (in this instance) as the
> highest ranking result?
> Thanks for your help!
>