在 MS SQL 2000 中自定义 FreeTextTable

发布于 2024-08-09 13:55:59 字数 839 浏览 5 评论 0原文

我正在寻找以下两个问题的答案（SQL Server 2000）。我有一个已索引的订单信息表，以便我可以搜索特定列上的数据。因此，我可能运行的一个示例查询是：

SELECT top 50 ft_tbl.*, key_tbl.Rank
from OrderInfo as ft_tbl
    INNER JOIN FREETEXTTABLE(OrderInfo, Address1, 'W Main St') as key_tbl
    ON ft_tbl.OrderInfoID = key_tbl.[KEY]
order by key_tbl.Rank desc

我期望 SQL 会首先提取与“W Main St”匹配的所有内容，因为它具有最高排名，然后是变化。然而，我的结果并不完全符合我的预期。以下是按排名排序的前 8 个结果：

258 W Main St
4322 N Marshall St
221 Main St
320 Broad St
7 S 3rd St
510 Bauerlein St
175 Main Street
108 Maywood St

（我知道为什么现在会发生这种情况，并且假设我可以用下面的答案修复它）

问题：有没有什么方法可以通过变体，其中 St 可以是：

St
St.
Street

，W 可以是

W
W.
West

提前致谢！（撞）

原文

I'm looking for answers for the following 2 questions (SQL Server 2000). I have an order info table that is indexed so that I may search data on particular columns. So, an example query I might run is:

SELECT top 50 ft_tbl.*, key_tbl.Rank
from OrderInfo as ft_tbl
    INNER JOIN FREETEXTTABLE(OrderInfo, Address1, 'W Main St') as key_tbl
    ON ft_tbl.OrderInfoID = key_tbl.[KEY]
order by key_tbl.Rank desc

What I'd expect is that SQL would pull everything that matches "W Main St" first since that would have the highest rank, then variations following. However, my results aren't exactly what I'm expecting. Here are the Top 8 results ordered by Rank: