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:
- 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
(I know why this happens now, and am assuming I can fix it with the answer below)
Question: Is there any way to pass in variations where St could be:
- St
- St.
- Street
And W could be
- W
- W.
- West
Thanks in advance! (bump)