How to write a sql to compare two strings and get the result as a percentage match?

[atksamy]

HI,


I am trying to compare a series of strings like the following

rodeo rodas
carrot crate
GLX GLX 1.1
GLX glxs

the comparision need not be case sensitive
i am trying to write a sql where i am updating the first string with the second string if they match approximately. Here except the second string all the other examples match. I would like to write a query which updates the strings except the second one.

is this possible directly in a query.

thanks

[ChipR]

Sure, this is possible in SQL if you string together 100 IIf statements. I wouldn't recommend it. You can, however, call a function from an update query, in which you determine whether a string should be updated. Realize though, determining whether strings "approximately match" is going to take quite a bit of effort.

[NeoPa]

Chip's on the money again of course.

If you've stopped at all to consider the complexity of code that would be required to implement such a loosely defined concept, you will know this is an extremely complex issue.

Consider that you weren't even able to define the question in a remotely logical way. That is a clue to how involved this all is.

As a direct answer to your question though, assuming you have a function available that will perform this type of comparison for you, yes you can include it in a query. It would need to be defined as Public in a standard module to be in scope, but if it were then it would be usable.

[atksamy]

well i have managed to find a function. The algorithm is based on Levenshtein distance where the number of changes required for the two strings to be equal is calculated. i am trying somehow to use the like function with this function to try to get the comparison

[NeoPa]

I can't imagine how that would work.

You would need a function that returned a value (how alike they are) and which took both strings as parameters. The Like statement would not be involved as the comparison work would need to be done within the function itself.

You will simply need to filter by the returned value, depending on your requirement.

[ajalwaysus]

I may look into this further later, but off the top of my head, what I visualize doing is building a function that would would pass to it both values, then you would have the function count, and as it increments it would compare character 1 of value 1 to character 1 of value 2 and then compare character 2 of value 1 to character 2 of value 2... and so forth and then you could do some percentages after that.

Maybe this has already occurred to you, but thought I would just put it out there.

-AJ

[ADezii]

You can use an Algorithm similar to the following which uses an arbitrary Word Length then scores each time a word in the search string matches a word in the source string. The score is a percentage of all possible matches that were detectable.

Public Function fCompareStrings(strSource As String, strSearch As String) As Single
Dim intCounter As Integer
Dim strTest As String
Dim intScore As Integer
Const conWORD_LENGTH As Integer = 3
 
For intCounter = 1 To Len(strSource) - conWORD_LENGTH
  strTest = Mid(strSource, intCounter, conWORD_LENGTH)
    If InStr(1, strSearch, strTest, vbTextCompare) > 0 Then
      intScore = intScore + 1
    End If
Next
 
fCompareStrings = 100 * (intScore / (Len(strSource) - conWORD_LENGTH))
End Function

[NeoPa]

I think he's looking for something that determines if words are similar ADezii, rather than if sentences are.

[ADezii]

@NeoPa
Missed another boat? (LOL)!

[FishVal]

@ADezii
Not really.
Your Q-gramm method implementation could be quite applicable here.

[NeoPa]

Absolutely Fish - If the words referred to are actually Q-Gramms. I missed that in my quick scan.