How to write a sql to compare two strings and get the result as a percentage match?  | Member | | Join Date: Oct 2008
Posts: 82
| | |
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
| | Expert | | Join Date: Jul 2008 Location: Maryland
Posts: 1,175
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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.
| | Member | | Join Date: Oct 2008
Posts: 82
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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.
| | Expert | | Join Date: Jul 2009 Location: KY
Posts: 253
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
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
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
I think he's looking for something that determines if words are similar ADezii, rather than if sentences are.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,214
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
Quote:
Originally Posted by NeoPa  I think he's looking for something that determines if words are similar ADezii, rather than if sentences are. Missed another boat? (LOL)!
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
Quote:
Originally Posted by ADezii Missed another boat? (LOL)! Not really.
Your Q-gramm method implementation could be quite applicable here.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,722
| | | re: How to write a sql to compare two strings and get the result as a percentage match?
Absolutely Fish - If the words referred to are actually Q-Gramms. I missed that in my quick scan.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,419 network members.
|
