Levenstein with * wildcards???? or other Search Suggestion..

  • Thread starter Thread starter Barry A&P
  • Start date Start date
B

Barry A&P

I am hoping i can use levenstein with some kind of like or wildcard i know
this is probably not possible but i am hoping the user can search for a
serial number using "AHP-52020" and have "AHP52020 WHITE" show up in the
results. i have used LIKE *AHP-52020* but sometimes a number or space or typo
might keep the desired result from showing. Is levenstein the answer or
should i look elsewhere?? How does Google get there???


I am having trouble with the LevenshteinDistance sample by Doug Steele
Just for experimentation I added my data to his demo form through the
following two queries the demo form searched only the lastnm field I want to
use the levenstein diff on my SerialNumber field but get the following error
when I put it in the lastnm field. “Data type mismatch in query “

This is the query with the error the tables SerialNumber field is text with
a length of 40
SELECT T_PartNumbers.Description AS firstnm, T_SerialNumbers.SerialNumber AS
lastnm
FROM T_PartNumbers RIGHT JOIN T_SerialNumbers ON T_PartNumbers.SKU =
T_SerialNumbers.SKU
WHERE (((T_PartNumbers.Description) Is Not Null) AND
((T_SerialNumbers.SerialNumber) Is Not Null))
ORDER BY T_SerialNumbers.SerialNumber;


This query Works fine (for wrong field) the tables PartNumber field is text
with a length of 25 is there a field length limit on Levenstein??

SELECT T_PartNumbers.Description AS lastnm, T_SerialNumbers.SerialNumber AS
Firstnm
FROM T_PartNumbers RIGHT JOIN T_SerialNumbers ON T_PartNumbers.SKU =
T_SerialNumbers.SKU
WHERE (((T_PartNumbers.Description) Is Not Null) AND
((T_SerialNumbers.SerialNumber) Is Not Null))
ORDER BY T_SerialNumbers.SerialNumber;

Thanks
Barry
 
I by not means an expect on fuzzy logic searches but recently I was thinking
of putting some of this in my application for
possible duplicate detection. Best I could find was the attachment on this
link:
http://www.kdkeys.net/forums/thread/6450.aspx

You have to sign up for it to work but the guy includes the VBA source code
for various algorithms (like Levenshtein Distance).

I'm sure Google's algorithm is much more complex. If I knew how it worked I
could get my websites to rank #1 and help others to do the same and would be
rich.

Most Access applications use regular sql techniques such as using the "like"
keyword. However you are right it won't work for
all types of searches.

Hope this helps,
Mark Andrews
RPT Software
http://www.rptsoftware.com
http://www.donationmanagementsoftware.com
 
John

The SQL samples are not used in code. Doug Steele has a little Demo MDB to
show the levenstein and soundex searches. his search data is from a query
qrycustomers and i just mimicked the query and inserted my data to play with
it but it only works with my PartNumber field (substituted in the lastnm
field) it does not work when i substitute my SerialNumbers field AS LastNm..

After reading the link from another poster this whole Fuzzy search may be
alot more cutting edge than i expected...

I am looking for a way to filter results of a query where the input value
may either be slightly misspelled or may have leading or trailing words
anything with phoenetic matching may not be applicable as i am only searching
serialnumbers.

The whole point to this database stuff is having good data.. maybe i just
need to clean up all of the serialnumber entries and stick with levenstein..

i am also going to try playing with levenstein distance taking into account
the length of the result string.

Ideas are greatly appreciated

thanks
Barry
 
Hi John
I hope youre still monitoring this.. I have been playing around to refine
my question and here is the code i am trying to get working...

If Nz(Me.SerialNumbertxt) <> "" Then
strwhere = " T_SerialNumbers.SerialNumber Like '*" & Me.SerialNumbertxt & "*'"
End If
'This works fine but i am also trying to also show records with a levenstein
distance

strwhere = strwhere & " and LevenshteinDistance([SerialNumber], """ &
Me.SerialNumbertxt & """) <= " Me.txtDistance

but i cant seem to get it right..
Thanks for your help
Barry
 
Back
Top