Fuzzy Search

[AlanAnderson]

I need to search for similar words in a file.

eg In a Master table the CoName might be "Ben's Boutique" - If in another file (imported data) there is a name "Bens Boutique" it would be great if one could either just accept it or ask user to confirm. Other examples might be Bens_Boutique, BENS BOUTIQUE, maybe even Ben's Buotique. Most likely would be names truncated in one file but not the other.
I got the following code from Captain Slog at UtterAccess which works well.

Function strip_char(phone As String) As String
Dim newphone As String
Dim x As Integer
phone = lcase(phone)
newphone = ""
For x = 1 To Len(phone)
'If Mid(phone, x, 1) Like "[0-9]" Then newphone = newphone + Mid(phone, x, 1)
If Mid(phone, x, 1) Like "[a-z]" Then newphone = newphone + Mid(phone, x, 1)

Next x
strip_char = newphone
End Function

. Other than really bad spelling it is picking up almost every example I can find except:

1. The word "and", +, & eg Matthew & Son vs Matthew and Son vs Matthew + Son
(This one worries me coz one can't just exclude the string "and" as it could be a legitimate part of a name. eg Anderson's Trading or Sandymans Tools etc)

2. Certain abbreviations - Limited vs Ltd; Company vs Co. (Suppose above concern exists here as well - Co could be part of "Coal Supplies" or company calling itself "FruitCo Limited"

Its not worth going crazy about it but if you can think of an easy way to include that as well it would be great
PS This has been posted on Utter Access as well.

Thanks,

Alan

 

[nanscombe]

How about adding:

  For x = 1 To Len(phone)
[COLOR="Red"]
    phone = Replace(phone, "+", "and")
    phone = Replace(phone, "&", "and")

    phone = Replace(phone, "Co.", "Company")
    phone = Replace(phone, " Co ", " Company ")
    If Right(phone, 3) = " Co") Then phone = phone & "mpany"

    phone = Replace(phone, "Ltd", "Limited")
[/COLOR]
    'If Mid(phone, x, 1) Like "[0-9]" Then newphone = newphone + Mid(phone, x, 1)
    If Mid(phone, x, 1) Like "[a-z]" Then newphone = newphone + Mid(phone, x, 1)

  Next x

At least you would be comparing like with like.

 

[AlanAnderson]

Thanks Nigel,

Thats good thinking

Regards

Alan

 

[nanscombe]

Mainly because ... "Been there. Done that."