Compare string contents ignoring order

  • Thread starter Thread starter Ted Collins
  • Start date Start date
T

Ted Collins

I need to set up a query that can compare the contents of
a couple of string expressions to see if they contain the
same characters, regardless of order.

Examples of what I mean:

"ABCDE" = "ABCDE"
"ABCDE" = "CDEAB"
"ABCDE" <> "DEFGH"
"ABCDE" <> "ABDE"

Anyone know how to set up a comparison like that?
 
Ted Collins said:
I need to set up a query that can compare the contents of
a couple of string expressions to see if they contain the
same characters, regardless of order.

Examples of what I mean:

"ABCDE" = "ABCDE"
"ABCDE" = "CDEAB"
"ABCDE" <> "DEFGH"
"ABCDE" <> "ABDE"

Anyone know how to set up a comparison like that?
Click to expand...

Please clarify: what about these cases?

"AABB" vs. "AB"
"AB" vs. "AABB"
 
Ted,

Don't know what you are doing your's for , but here is a
little something that I whipped up when my mother started
getting the better of me in Scrabblegrams. Never really did
use it on her, but it was a tempting thought <gr>.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR

******************
Sub FindWord(strLetters As String)
Dim db As Database, rs As Recordset
Dim l1 As String, l2 As String, l3 As String
Dim l4 As String, l5 As String, l6 As String
Dim sql As String

l1 = Mid(strLetters, 1, 1)
l2 = Mid(strLetters, 2, 1)
l3 = Mid(strLetters, 3, 1)
l4 = Mid(strLetters, 4, 1)
l5 = Mid(strLetters, 5, 1)
l6 = Mid(strLetters, 6, 1)

sql = "Select Dictionary.Word FROM Dictionary WHERE
Instr([Word],'" & l1 & "') > 0 " & _
"AND Instr([Word],'" & l2 & "') > 0 AND Instr([Word],'" & l3
& "') > 0 AND Instr([Word],'" & l4 & "') > 0 " & _
"AND Instr([Word],'" & l5 & "') > 0AND Instr([Word],'" & l6
& "') > 0 ;"


Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
If .EOF And .BOF Then
MsgBox "No Matches"
Exit Sub
End If
.MoveFirst
Do Until .EOF
MsgBox !Word
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub

________________________
 
-----Original Message-----
Please clarify: what about these cases?

"AABB" vs. "AB"
"AB" vs. "AABB"
Click to expand...

Character's should never repeat in the compare I'm
running. The letters represent permissions, and the
comparison is a validation.

Thanks,
Ted C
 
Try writing a function (let's call it SortedString) that splits the string
into the individual characters, sorts the characters alphabetically, then
concatenates them back into a string. Then you can check if
SortedString(string1) = SortedString(string2)

The following untested air-code should get you going:

Function SortedString(StringIn As String) As String
Dim lngLength As String
Dim lngLoop As String
Dim lngLoop1 As String
Dim strInput() As String

lngLength = Len(StringIn)
Redim strInput(1 to lngLength)
For lngLoop = 1 to lngLength
strInput(lngLoop) = Mid$(StringIn, lngLoop, 1)
Next lngLoop

Call QuickSort(strInput, 1, lngLength)

for lngLoop = 1 to lngLength
SortedString = SortedString & strInput(lngLoop)
Next lngLoop

End Function

Public Sub QuickSort(iArray As Variant, l As Long, r As Long)
'iArray() The iArray to sort
'l First element of iArray to start sort
'r Last element of iArray to start sort

Dim X As Long
Dim Y As Long
Dim i As Long
Dim j As Long

i = l
j = r
X = iArray((l + r) / 2)

While (i <= j)

While (iArray(i) < X And i < r)
i = i + 1
Wend

While (X < iArray(j) And j > l)
j = j - 1
Wend

If (i <= j) Then
Y = iArray(i)
iArray(i) = iArray(j)
iArray(j) = Y
i = i + 1
j = j - 1
End If

Wend

If (l < j) Then QuickSort iArray, l, j
If (i < r) Then QuickSort iArray, i, r

End Sub

I just stole the QuickSort routine from Randy Birch
http://www.mvps.org/vbnet/code/sort/qscompare.htm You can substitute your
own favourite sort instead.
 
Character's should never repeat in the compare I'm
running. The letters represent permissions, and the
comparison is a validation.

Thanks,
Ted C
Click to expand...

That implies the strings must be the same length. If you can rely on
the characters not to repeat in either string, then I think this should
do the job:

'----- start of code -----
Function StringsHaveSameCharacters( _
String1 As String, String2 As String) _
As Boolean

Dim lngX As Long

If Len(String1) = Len(String2) Then

For lngX = 1 To Len(String1)
If InStr(String2, Mid$(String1, lngX, 1)) = 0 Then
Exit Function
End If
Next lngX

StringsHaveSameCharacters = True

End If

End Function
'----- end of code -----

There may be something in the Regular Expressions library that would be
quicker, but I'm not familiar with the ins and outs of that library.
 
-----Original Message-----
That implies the strings must be the same length. If you can rely on
the characters not to repeat in either string, then I think this should
do the job:

'----- start of code -----
Function StringsHaveSameCharacters( _
String1 As String, String2 As String) _
As Boolean

Dim lngX As Long

If Len(String1) = Len(String2) Then

For lngX = 1 To Len(String1)
If InStr(String2, Mid$(String1, lngX, 1)) = 0 Then
Exit Function
End If
Next lngX

StringsHaveSameCharacters = True

End If

End Function
'----- end of code -----

There may be something in the Regular Expressions library that would be
quicker, but I'm not familiar with the ins and outs of that library.
Click to expand...

Well, if the strings aren't the same length, the
comparison should return false, since they have to be the
same length to be a match. At first glance, this looks
like it should work.

Thanks,
Ted Collins
 
Dirk,

Nice tight code. Mine seems awful kludgy after seeing yours.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
 
Ted,

My approach was to feed the search string to a sub, divide
it into it's letters ( 6 in this example ) using the Mid
statements and then to construct a SQL statement that says
that 'A' needs to be in the field being compared as well as
'B' and 'C', etc... It would be easy to say that the Length
needs to be 6 or however many letters as well. I then used
the SQL to populate a recordset of the matches and looped
through it to display in a message box for my purposes at
the time.

I suppose that you could also dump the results to a temp
table through modifying it to a MakeTable query.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________
Gary,

Thanks! This may be a bit over my head, but I'll see if I
can figure it out.

Ted

-----Original Message-----
Ted,

Don't know what you are doing your's for , but here is a
little something that I whipped up when my mother started
getting the better of me in Scrabblegrams. Never really did
use it on her, but it was a tempting thought <gr>.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR

******************
Sub FindWord(strLetters As String)
Dim db As Database, rs As Recordset
Dim l1 As String, l2 As String, l3 As String
Dim l4 As String, l5 As String, l6 As String
Dim sql As String

l1 = Mid(strLetters, 1, 1)
l2 = Mid(strLetters, 2, 1)
l3 = Mid(strLetters, 3, 1)
l4 = Mid(strLetters, 4, 1)
l5 = Mid(strLetters, 5, 1)
l6 = Mid(strLetters, 6, 1)

sql = "Select Dictionary.Word FROM Dictionary WHERE
Instr([Word],'" & l1 & "') > 0 " & _
"AND Instr([Word],'" & l2 & "') > 0 AND Instr([Word],'" & l3
& "') > 0 AND Instr([Word],'" & l4 & "') > 0 " & _
"AND Instr([Word],'" & l5 & "') > 0AND Instr([Word],'" & l6
& "') > 0 ;"


Set db = CurrentDb
Set rs = db.OpenRecordset(sql)
With rs
If .EOF And .BOF Then
MsgBox "No Matches"
Exit Sub
End If
.MoveFirst
Do Until .EOF
MsgBox !Word
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Set db = Nothing

End Sub
Click to expand...
Click to expand...
 
As far as I am concerned, I always avoid making any comparisons whatsoever
where Dirk is concerned. It inevitably leads me to have feelings of ... how
can I put it....um.... inadequacy? <g>

Cheers!
Fred Boer
 
Gary Miller said:
Dirk,

Nice tight code. Mine seems awful kludgy after seeing yours.
Click to expand...

Thanks, Gary -- though of course yours was written for a different
problem. Now we'll leave it to Ted Collins to tell us if the function
actually works.
 
Fred Boer said:
As far as I am concerned, I always avoid making any comparisons
whatsoever where Dirk is concerned. It inevitably leads me to have
feelings of ... how can I put it....um.... inadequacy? <g>
Click to expand...

<blush>
 
If I weren't such an amateur, you'd already have an
answer. As it is, I still have to figure out how to
incorporate this into a query.
 
If I weren't such an amateur, you'd already have an
answer. As it is, I still have to figure out how to
incorporate this into a query.
Click to expand...

<g>

Save the function in a standard module, making sure that the name of the
module itself is not the same as the name of the function.

In a query, invoke the function with SQL along these lines:

SELECT * FROM MyTable
WHERE StringsHaveSameCharacters([Field1], [Field2]);

That should return all the records from MyTable where the fields Field1
and Field2 have all the same characters. One note: this function as
written will fail if Field1 or Field2 is Null. If there's a chance of
that being the case, you could rewrite the function slightly to handle
it, or you could protect yourself in the call to the function itself:

SELECT * FROM MyTable
WHERE StringsHaveSameCharacters(Nz([Field1],""), Nz([Field2],""));
 
-----Original Message-----
Save the function in a standard module, making sure that the name of the
module itself is not the same as the name of the function.
Accomplished.

In a query, invoke the function with SQL along these lines:

SELECT * FROM MyTable
WHERE StringsHaveSameCharacters([Field1], [Field2]);
Click to expand...

The function doesn't handle null fields very well, but
this ended up finding the discrepancies not involving
nulls.

SELECT [TableName].Module,
[TableName].FormID,
[TableName].ClassID,
[TableName].FCs,
TOKEN.AC,
StringsHaveSameCharacters
([TableName].FCs,TOKEN.AC) AS Compare

FROM [TableName] INNER JOIN TOKEN ON ([TableName].ClassID
= TOKEN.SEC_CLASS)
AND ([TableName].FormID = TOKEN.FORM)

WHERE ((([TableName].FCs) Is Not Null)
AND ((TOKEN.AC) Is Not Null)
AND ((StringsHaveSameCharacters([TableName].[FCs],
[TOKEN].[AC]))=False));


Ted Collins
 
Ted Collins said:
-----Original Message-----
Save the function in a standard module, making sure that the name of
the module itself is not the same as the name of the function.
Accomplished.

In a query, invoke the function with SQL along these lines:

SELECT * FROM MyTable
WHERE StringsHaveSameCharacters([Field1], [Field2]);
Click to expand...

The function doesn't handle null fields very well, but
this ended up finding the discrepancies not involving
nulls.

SELECT [TableName].Module,
[TableName].FormID,
[TableName].ClassID,
[TableName].FCs,
TOKEN.AC,
StringsHaveSameCharacters
([TableName].FCs,TOKEN.AC) AS Compare

FROM [TableName] INNER JOIN TOKEN ON ([TableName].ClassID
= TOKEN.SEC_CLASS)
AND ([TableName].FormID = TOKEN.FORM)

WHERE ((([TableName].FCs) Is Not Null)
AND ((TOKEN.AC) Is Not Null)
AND ((StringsHaveSameCharacters([TableName].[FCs],
[TOKEN].[AC]))=False));


Ted Collins
Click to expand...

Did you see my note about how to use the function even with Null fields,
by wrapping the fields in the Nz() function? And if this is something
you're going to run often, we could easily rewrite the function to cope
with Nulls even without using Nz(). Aw, what the heck; here it is:

'----- start of code -----
Function StringsHaveSameCharacters( _
StringVar1 As Variant, StringVar2 As Variant) _
As Boolean

Dim String1 As String
Dim String2 As String
Dim lngX As Long

' Convert Null arguments to zero-length strings.
String1 = StringVar1 & vbNullString
String2 = StringVar2 & vbNullString

If Len(String1) = Len(String2) Then

For lngX = 1 To Len(String1)
If InStr(String2, Mid$(String1, lngX, 1)) = 0 Then
Exit Function
End If
Next lngX

StringsHaveSameCharacters = True

End If

End Function
'----- end of code -----

That ought to do it.
 
-----Original Message-----
Did you see my note about how to use the function even with Null fields,
by wrapping the fields in the Nz() function?
Click to expand...

Shortly after sending my previous message, of course (dope
slap self-administered).
And if this is something
you're going to run often, we could easily rewrite the function to cope
with Nulls even without using Nz(). Aw, what the heck; here it is:

'----- start of code -----
Function StringsHaveSameCharacters( _
StringVar1 As Variant, StringVar2 As Variant) _
As Boolean

Dim String1 As String
Dim String2 As String
Dim lngX As Long

' Convert Null arguments to zero-length strings.
String1 = StringVar1 & vbNullString
String2 = StringVar2 & vbNullString

If Len(String1) = Len(String2) Then

For lngX = 1 To Len(String1)
If InStr(String2, Mid$(String1, lngX, 1)) = 0 Then
Exit Function
End If
Next lngX

StringsHaveSameCharacters = True

End If

End Function
'----- end of code -----

That ought to do it.
Click to expand...

Getting ready to test it.

Any idea where I should try to fit a Trim() function into
this to eliminate the ones that just have extra spaces?
Quite a few of those showing up, too.

Ted
 
Doh! Answered my own question. I trimmed at the same
time as we were fixing the nulls.

Ted
 
Well, if the strings aren't the same length, the
comparison should return false, since they have to be the
same length to be a match. At first glance, this looks
like it should work.
Click to expand...


I always hate accepting conditions like "the input will always be..."
(i.e., yeah, right - and the "the check's in the mail" too)


Thirty or so years ago, writing COBOL to put myself through college, I got
those kinds of data guarantees all the time. And I pretty much either ignored
the promise and coded the unrequested data checks, or else they came back to me
to rework the programs when the promised conditions proved in fact to be unreliable.
AND half the time tried to blame me for the bugs - funny how I always held onto
those specification documents where the promises were made...

So now I always insist on a decision. Either it's okay for everything else to
turn to [excrement] when the promised condition fails, or else it should be
handled in the code.


For example, Dirk's code *will* equate "AA" to "AB" (though not vice-versa).
If you're okay with that kind of failure mode, then great.

From your description of them as flags, it's likely to be much safer to say
that if duplicates happen, then extra occurrances of a character don't change
the meaning. Thus "AABAA" would be allowed to match "BBA"

My first approach to this problem probably would have resembled Douglas' sorting
idea, using a sort algorithm which removes duplicates. But I like Dirk's "is in"
approach as well. It would just need to be applied in both directions:

For each character in string1 if it's not in string2 then exit with a mismatch.
For each character in string2 if it's not in string1 then exit with a mismatch.
Exit with a match.



Bob
 
Back
Top