# Thread: Difference Function Soundex

1. Registered User
Join Date
Nov 2007
Posts
1

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?

2. Stuck 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```
What the heck....name the LD function "Difference" if you like.

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.

.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•