Results 1 to 2 of 2
Thread: Difference Function Soundex

112307, 13:22 #1Registered User
 Join Date
 Nov 2007
 Posts
 1
Unanswered:
I am trying to use the soundex and difference functions in MS ACCESS. I found the code for the soundex function, but I cannot find the code for the difference function. Can anyone help?

120307, 03:05 #2Stuck on my opinions...
 Join Date
 Nov 2003
 Posts
 1,487
I was stumbling through some older posts and come across yours.
First off....Welcome to dbforums
Secondly...Sorry for the late response.
Just a thought here....(and this is the way I would go)... how about considering the implimentation of the Levenshtein String Distance Algorithm. In my opinion, I think the Difference function utilizes this algorithym anyways but more so in a limited fashion.
The Levenshtein Distance (or edit distance) is a measure of the similarity between two strings. The distance is the number of deletions, insertions, or substitutions required to transform String1 into String2. For example:
If String1 is "Test" and String2 is "Test", then LD(String1,String2) = 0, because no transformations are needed. The strings are already identical.
If String1 is "Test" and String2 is "Tent", then LD(String1,String2) = 1, because one substitution (change "s" to "n") is sufficient to transform String1 into String2. The greater the Levenshtein Distance, the more difference there is between the two Strings.
The Difference function provides a comaprison difference from 0 to 4 with 0 considered to be the closest match a 4 considered to be the least likely match. With the Levenshtein Distance you can go even deeper with 0 still considered as the closest or exact match. A little more flexability I guess.
For Example, with the Function below which was originally created by Michael Gilleland, if you supply:
intRet = LD("dbforums", "dbforums")
intRet will be 0 becuase it is an exact match. If you supply:
intRet = LD("dbforums", "dbForums")
intRet will be 1. The LD function is case sensitive. If you don't care for that much accuracy then convert both strings to either all lower or upper case when supplying them to the LD function:
intRet = LD(UCase("dbforums"), UCase("dbForums"))
intRet will be 0
To see a greater distance value supply:
intRet = LD("dbforums", "This is far from that")
intRet will be 18
Here is the LD Function code. It is also comes with another required function named Minimum which the LD function accesses:
Code:'******************************* '*** Get minimum of three values '******************************* Private Function Minimum(ByVal a As Integer, _ ByVal b As Integer, _ ByVal c As Integer) As Integer Dim mi As Integer mi = a If b < mi Then mi = b End If If c < mi Then mi = c End If Minimum = mi End Function '******************************** '*** Compute Levenshtein Distance '******************************** Public Function LD(ByVal StringA As String, ByVal StringB As String) As Integer Dim d() As Integer ' matrix Dim m As Integer ' length of StringB Dim n As Integer ' length of StringA Dim i As Integer ' iterates through StringA Dim j As Integer ' iterates through StringB Dim s_i As String ' ith character of StringA Dim t_j As String ' jth character of StringB Dim cost As Integer ' cost ' Step 1 n = Len(StringA) m = Len(StringB) If n = 0 Then LD = m Exit Function End If If m = 0 Then LD = n Exit Function End If ReDim d(0 To n, 0 To m) As Integer ' Step 2 For i = 0 To n d(i, 0) = i Next i For j = 0 To m d(0, j) = j Next j ' Step 3 For i = 1 To n s_i = Mid$(StringA, i, 1) ' Step 4 For j = 1 To m t_j = Mid$(StringB, j, 1) ' Step 5 If s_i = t_j Then cost = 0 Else cost = 1 End If ' Step 6 d(i, j) = Minimum(d(i  1, j) + 1, d(i, j  1) + 1, d(i  1, j  1) + cost) Next j Next i ' Step 7 LD = d(n, m) Erase d End Function
I use both the SoundEx and LD function all the time for enhanced search operations on specific fields among other things. When I get time I'll submit a sample Search Form utilizing these functions among several other things.
.Environment:
Self Taught In ALL Environments.....And It Shows!