I am implementing Web site functionality whereby a user who enters a string
(which will usually not contain spaces/words) will be presented with the
nearest pre-existing string that matches their entry. Ideally, from what
I've seen of SQL Server 2005's functionality, I would like to apply Fuzzy
Lookup to the user's entry; however, that functionality appears to be
limited to ETL (i.e. batched external data loading) processing, not
real-time single-value lookup. A data mining query might meet my
requirements, but my understanding of the available data mining models (such
as text mining) is that they are word-based or otherwise not appropriate for
this application.

Is there some version of Fuzzy Lookup functionality in SQL Server 2005 that
would be appropriate for this real-time loose-string lookup?

Thanks in advance,

-- Brian Cowhig

Would something like Full Text search be more applicable?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm

Allan

"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote

The data mining APIs are exposed for this type of scenario, the fuzzy lookup
is not, it is only available through a package.

Data mining is perhaps more appropriate to validate input, how likely is it
that your input fields are correct, the 13 year old boy with 12 kids is
unlikely, well it would be against my data in a trained model.

Have a search for soundex and related algorithms. There are several out
there.

Perhaps some domain validation may be better, such as using a postcode field
to help populate an address.

A simple drop down of values, rather than free text. If you allow free text
as well as drop-down you could then text mine this later to determine new
values for the drop-down.

--
Darren Green
http://www.sqldts.com
http://www.sqlis.com


"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote

Thanks for your reply, Allan. However, my experience with full-text indexing is that it only operates on whole words, separated by spaces, hyphens, or other delimiters. In this case, I want to be able to match on similar strings that are not broken into words. For example, 'ABC123' would have a stronger match with '123ABC' than with 'ABC'. From what I've seen (and researched) of Integration Services' Fuzzy Lookup and Fuzzy Grouping processing, it has the capability of doing loose (or fuzzy) matching such as that, but only during ETL activities, not as part of typical transactional database activities.

-- Brian
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Would something like Full Text search be more applicable?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm

Allan

"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote

Yep. Transactional SSIS is probably not your bag then. As for FTS
Hilary Cotter is the man. If he doesn't know it then it can't be done.
I'll send him your way.

Allan

"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote

Basically what you want to do is build an inverted file index and then do a
like to get a list of rows with the match.

If you know for a fact that you would only be looking at the first 4 or 7
lets say letters you would index only the first 4 or 7 letters and then not
have to do the relatively expensive like. Note your like here will perform
better than a full column like.

Hilary
"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote

Thanks for your reply, Allan. However, my experience with full-text
indexing is that it only operates on whole words, separated by spaces,
hyphens, or other delimiters. In this case, I want to be able to match on
similar strings that are not broken into words. For example, 'ABC123' would
have a stronger match with '123ABC' than with 'ABC'. From what I've seen
(and researched) of Integration Services' Fuzzy Lookup and Fuzzy Grouping
processing, it has the capability of doing loose (or fuzzy) matching such as
that, but only during ETL activities, not as part of typical transactional
database activities.

-- Brian
"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote

Would something like Full Text search be more applicable?

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/996c72fc-b1ab-4c96-bd12-946be9c18f84.htm

Allan

"Brian Cowhig" <BCowhig (AT) yahoo (DOT) com> wrote