search with probability

[Pieter]

Hi,

I need some search system, that will return me the records with in indicator
of probability. This is for a VB.NET 2005 application with a SQL Server 2000
DataBase.
I have a table with different columns in which the user needs to search. In
case the search-routine finds the exact match (the same part of the
sentence) it should return that record with a 100%-indicator. If it finds
all the words in it, but not in that order: 75%-indicator. If it find only
some words in it: 50%-indicator. If it finds only some parts of some words
in it: 25%-indicator.

You can see this sometimes on websites that allow you to search for
keywords.

I can't find any sample code for this, and I don't know exactly how to
implement this. It should be as fast as possible! Should I do this at
DataBase-level, or read the whole table in my VB.NET-application and perform
the Search there?

Any help, hits, samples, ... would be great!

Thanks a lot in advance,

Pieter

 

[Ken Tucker [MVP]]

Hi,

Take a look at FreeTextTable and ContainsTable sql queries.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_fa-fz_1f51.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_3syt.asp

Ken

 

[Pieter]

Wow! At first sight this seems something very powerfull! I'll need to take a
closer look at it!

Just a pitty that it seems to work only on Tables, not on views? Or is it
possible to use it on a View when the underlying tables contain it?

Thanks a lot!

 

[Pieter]

This seems to work fine in most cases, but one very important case it
doesn't: When the user has typed only a part of the word, it doesn't work.
For instance: If you repalce in the query "bread" with "bre" it doesn't
return all the records with "bre" in it.... any idea how to add wilcards to
this?

USE Northwind
SELECT FT_TBL.CategoryName,
FT_TBL.Description,
KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN
FREETEXTTABLE(Categories, Description,
'sweetest candy bread and dry meat') AS KEY_TBL
ON FT_TBL.CategoryID = KEY_TBL.[KEY]