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:

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!

.

Relevant Pages

  • Re: Which Operator is fast on Varchar - Indexed Column?
    ... > I would expect query A and a corrected query B to run equally fast. ... > Prabhat wrote: ... >> I have one table with Millions of records with a Varchar Column which is ...
    (microsoft.public.sqlserver.programming)
  • Re: Which Operator is fast on Varchar - Indexed Column?
    ... I would expect query A and a corrected query B to run equally fast. ... Prabhat wrote: ... > I have one table with Millions of records with a Varchar Column which is ...
    (microsoft.public.sqlserver.programming)
  • Re: Select instruction not returning entire column (implicit truncate ???)
    ... If indeed the data is, as you've posted, in a VARCHAR column, and if nobody ... >>The following instructions are actually for SQL Server ... >>characters per column setting. ...
    (microsoft.public.sqlserver.clients)
  • Re: How to tell if I can convert a varchar to int
    ... I support the Professional Association for SQL Server ... > value in a varchar column (which may contain a mix of words and number ... > need is a means of telling whether a value can be cast as an int before ...
    (microsoft.public.sqlserver.server)
  • Re: How to tell if I can convert a varchar to int
    ... I support the Professional Association for SQL Server ... > value in a varchar column (which may contain a mix of words and number ... > need is a means of telling whether a value can be cast as an int before ...
    (microsoft.public.sqlserver.programming)