Comparing two Strings

[mousie]

Hey Guys,

Just wondering how I would go about comparing two strings, and spitting the results out a similarity percentage?

E.G. (String 1) Postal Address: "11 John St"
(String 2) Street Address: "11 John Street"
(Output): 80%?

Cheers

 

[Moniker]

The correct output for that is 71.4% (10 characters/14 characters).

See the attached for how I did it. It should be very easy to turn what I did into a function that can be called something like:

PercentDifferent = StringCompare(string1, string2)

 

[mousie]

Wow, this looks awesome, I'll plug it into my current DB and let you know how it goes!

Cheers mate!

 

[mousie]

Yep, it worked.

I've converted it to a public function below, but thanks heaps for the groundwork!


Public Function str_Perc(txtString1 As String, txtString2 As String)


Dim str1Len As Integer
Dim str2Len As Integer
Dim strLenDiff As Integer
Dim ctr As Integer
Dim strLenMax As Integer
Dim NumMatches As Single

str1Len = Len(txtString1)
str2Len = Len(txtString2)

strLenDiff = Switch(str2Len <= str1Len, str1Len - str2Len, True, str2Len - str1Len)
strLenMax = Switch(str2Len <= str1Len, str1Len, True, str2Len)

For ctr = 1 To strLenMax
NumMatches = NumMatches + Switch(Mid(txtString1, ctr, 1) = Mid(txtString2, ctr, 1), 1, True, 0)
Next

str_Perc = NumMatches / strLenMax * 100

End Function

 

[Moniker]

Glad it worked out. I knew it'd be easy to convert to a function.

FYI, you probably want to write the first line of that function slightly differently. With no specification, it's returning a Variant, the slowest variable Access has. Change the function declaration to this (and keep the rest the same):

Public Function str_Perc(txtString1 As String, txtString2 As String) As Single

The As Single at the end will return a number that is easy to represent as a percentage. The Variant will do it, but not as efficiently. You won't notice it with 10 or 100 records, but you will notice a difference with 10,000 or 100,000.

EDIT:

I was on crack (metaphorically) when I posted that. The variable strLenDiff is superfluous, so remove its declaration (Dim strLenDiff As Integer) and the definition (strLenDiff = Switch(str2Len <= str1Len, str1Len - str2Len, True, str2Len - str1Len)) as they are unused, like so:

Public Function str_Perc(txtString1 As String, txtString2 As String) As Single

    Dim str1Len As Integer
    Dim str2Len As Integer
    Dim ctr As Integer
    Dim strLenMax As Integer
    Dim NumMatches As Single

    str1Len = Len(txtString1)
    str2Len = Len(txtString2)
    strLenMax = Switch(str2Len <= str1Len, str1Len, True, str2Len)

    For ctr = 1 To strLenMax
        NumMatches = NumMatches + Switch(Mid(txtString1, ctr, 1) = Mid(txtString2, ctr, 1), 1, True, 0)
    Next

    str_Perc = NumMatches / strLenMax * 100

End Function

And finally, note the SWITCH function I used in there a lot. It's very useful and a lot cleaner than If..Then (which it nearly replaces). Search Access Help for that one if you're unsure what I'm doing.

 

[mousie]

Cool, good to know about defining the output datatype, although I wouldn't say it was sluggish on 70k.