Query to compare string combination

[Erick]

I need a query to compare combination of concatenated string so that "A, B, C" compared to B, A, C will return True, whilst "A, B, C" compared to "B, A, A" will return false. Effectively if I compare 2 string records, it will look at the combination (comma delimited) and will return true if the comma delimited strings are the same, regardless of the position.

Can this be done without VBA?

 

[Micron]

You mean an Access solution without code? I don't think so, because of the potential for input variation. Don't bother trying to put the individual elements into fields of a Crosstab query IMHO, because the number of fields would fluctuate, thus you could end up trying to refer to a field that doesn't exist next time you run it.

My approach would be to sum each element's character value (Ascii) and compare the two sums. Would not matter how many elements there are if you make them an array. HOWEVER, I admit it could be possible that the sums of x number of characters vs y could be the same if there were many low value characters vs not as many high ones. Based on your post, I'd guess the likely-hood is nil since each string contained elements from the other. Here's the code for it:

Sub compStrings()
Dim str1 As String, str2 As String
Dim lngSum1 As Long, lngSum2 As Long
Dim ary1, ary2
Dim i As Integer

str1 = "a,b,d": str2 = "b,a,c"
ary1 = Split(str1, ",")
ary2 = Split(str2, ",")
For i = 0 To UBound(ary1)
    lngSum1 = Asc(LCase(ary1(i)))
Next
For i = 0 To UBound(ary2)
    lngSum2 = Asc(LCase(ary2(i)))
Next

If lngSum1 = lngSum2 Then
    MsgBox "equal"
Else
    MsgBox "different"
End If
    
End Sub

I chose to force each element to lower case so that A is no different than a, because those Ascii values are not the same. A little less code might be achievable by using the same function pass the string and process each sum, but not much. If the above was a function, you could call it from a query, but that's about the only way I can see a query being involved.

 

[xenou]

Adding more to the above answers:

I came up with some ideas for sql only solutions but with limitations. Still, might work if:
1) there are a limited number of values that might match, i.e. {'A','B',...,'F'} instead of {'A','B','C', ... 'Z'}
and/or
2) there are not duplicates within the categories, i.e. CAT1 can contain A,B,C or D,E,F but never A,A,B or D,F,F

If it's relevant, you have not yet answered my question in Post 8.

 

[Erick]

Hi Xeniou, apologies for not responding to your query in Post 8. I actually missed that one.
To answer your question in Post 8, I am basically comparing if the elements contained within categories are the same. The number of categories will be variable, as well as the number of elements within.
With regards to your query on Post12, each category will contain on average of around 8 to 12 elements, however this will be variable. However, there will be a lot of potential elements. This A, B, C record set represents a string record with up to 32 characters. To make it easier to manage, I have created a query to assign an index number to each unique record. This means that in my comparison query, I will be comparing the numbered indices, as opposed to straight text.
To your second question, given the nature of data, there shouldn't be duplicate record per category.
My original approach to solve this was to pass the raw query output into a dynamic array, then sort the array record, then concatenate and compare. I think I was just being too ambitious in trying to achieve this with sql only solution.

 

[Erick]

You mean an Access solution without code? I don't think so, because of the potential for input variation. Don't bother trying to put the individual elements into fields of a Crosstab query IMHO, because the number of fields would fluctuate, thus you could end up trying to refer to a field that doesn't exist next time you run it.

My approach would be to sum each element's character value (Ascii) and compare the two sums. Would not matter how many elements there are if you make them an array. HOWEVER, I admit it could be possible that the sums of x number of characters vs y could be the same if there were many low value characters vs not as many high ones. Based on your post, I'd guess the likely-hood is nil since each string contained elements from the other. Here's the code for it:

Sub compStrings()
Dim str1 As String, str2 As String
Dim lngSum1 As Long, lngSum2 As Long
Dim ary1, ary2
Dim i As Integer

str1 = "a,b,d": str2 = "b,a,c"
ary1 = Split(str1, ",")
ary2 = Split(str2, ",")
For i = 0 To UBound(ary1)
    lngSum1 = Asc(LCase(ary1(i)))
Next
For i = 0 To UBound(ary2)
    lngSum2 = Asc(LCase(ary2(i)))
Next

If lngSum1 = lngSum2 Then
    MsgBox "equal"
Else
    MsgBox "different"
End If
    
End Sub

I chose to force each element to lower case so that A is no different than a, because those Ascii values are not the same. A little less code might be achievable by using the same function pass the string and process each sum, but not much. If the above was a function, you could call it from a query, but that's about the only way I can see a query being involved.

Thanks for your response, Micron. I have actually done something similar, but your solution is better. I have since created a numbered index for each of my sting record and then summed the indices. In your solution, by using the ASC function, I think it means that I won't have to create an index...I'll trial this out.