• Home
 · 
• String Manipulation
 · 
• A Smart String Comparison

A Smart String Comparison

This takes 2 strings and returns the percent alike that they are. (i.e. "test string number 1" is 86.48% similar to "teststring numb 2")
This function is very useful! You can use it in databases to match data that may have errors in it. Examples being people's names, company names, addresses, or anything else where you may encounter misspellings or inconsistencies in the data. Your feedback and/or votes are greatly appreciated! -- NEW - updated to use byte arrays instead of strings, 50-300% performance improvement!
An implementation of the , Ratcliff/Obershelp/Levenshtein method.

Inputs
mainstring and checkstring, the 2 strings to compare

Assumes
This code recursively loops through the 2 strings, finding the largest common substring, then checking the remainder of the string.

Returns
how similar the 2 strings are (percent, as in .8)

Rate A Smart String Comparison

• 1
• 2
• 3
• 4
• 5

(35(35 Vote))

Private b1() As Byte
Private b2() As Byte
Public Function Simil(String1 As String, String2 As String) As Double
 Dim l1 As Long
 Dim l2 As Long
 Dim l As Long
 Dim r As Double
 If UCase(String1) = UCase(String2) Then
  r = 1
 Else
  l1 = Len(String1)
  l2 = Len(String2)
  If l1 = 0 Or l2 = 0 Then
   r = 0
  Else
   ReDim b1(1 To l1): ReDim b2(1 To l2)
   For l = 1 To l1
    b1(l) = Asc(UCase(Mid(String1, l, 1)))
   Next
   For l = 1 To l2
    b2(l) = Asc(UCase(Mid(String2, l, 1)))
   Next
   r = SubSim(1, l1, 1, l2) / (l1 + l2) * 2
  End If
 End If
 Simil = r
 Erase b1
 Erase b2
End Function
Private Function SubSim(st1 As Long, end1 As Long, st2 As Long, end2 As Long) As Long
 Dim c1 As Long
 Dim c2 As Long
 Dim ns1 As Long
 Dim ns2 As Long
 Dim i As Long
 Dim max As Long
 If st1 > end1 Or st2 > end2 Or st1 <= 0 Or st2 <= 0 Then Exit Function
 For c1 = st1 To end1
  For c2 = st2 To end2
   i = 0
   Do Until b1(c1 + i) <> b2(c2 + i)
    i = i + 1
    If i > max Then
     ns1 = c1
     ns2 = c2
     max = i
    End If
    If c1 + i > end1 Or c2 + i > end2 Then Exit Do
   Loop
  Next
 Next
 max = max + SubSim(ns1 + max, end1, ns2 + max, end2)
 max = max + SubSim(st1, ns1 - 1, st2, ns2 - 1)
 SubSim = max
End Function