0

I have one column array that I'm selecting from a SQL Server, this column is sometimes ascending sorted and sometimes not.

The database I'm selecting from is set to Hebrew_CI_AS collation, and the data in the column contains Hebrew text, English text and wildcards.

In my VBA sub I have this code that should check rather the "ArrayThatReturnsFromSQL" variable is ascending sorted or not.

Sub MySub
     Dim ArrayThatReturnsFromSQL as Variant
     Dim IsArraySorted as Boolian

     'One column array
     ArrayThatReturnsFromSQL = ThisFunctionReturnsArrayFromSQL

     IsArraySorted = IsArrSortedAsc(ArrayThatReturnsFromSQL) 

End Sub


Public Function IsArrSortedAsc(Arr As Variant) as Boolean
     Dim aRw As Long
     Dim Ub As Long
     Dim tmpBool As Boolean

     Ub = UBound(Arr)
     tmpBool = True

     If Ub > 0 Then
         For aRw = 1 To Ub
             If Arr(aRw) < Arr(aRw - 1) Then
                 tmpBool = False
                 Exit For
             End If
         Next
     End If
    
     IsArrSortedAsc = tmpBool
End Function

My IsArrSortedAsc is always returns FALSE even after an "ORDER BY ASC" clause, and I think it's because my Hebrew_CI_AS collation.

I think I can solve it by adding a ORDER BY FieldName COLLATE ????????, but I don't know what is the VBA "Collation"...

So my question is, what Collation I need to use in the SQL that is similar to the way the VBA ordering is working ?

EDIT:

Ok, I managed to isolate the issue, try the following code, it is clearly a VBA bug.

Sub Test()
    Dim STR1 As String
    Dim STR2 As String
    
    'Reminder, Hebrew is written from right to left
    'so STR1 should be less then STR2
    STR1 = "א'"
    STR2 = "'א"
    
    
    'this return 1 it means that string1 is greater than string2, and it's wrong.
    Debug.Print StrComp(STR1, STR2, vbTextCompare)
End Sub

Any ideas ?

xl0911
  • 108
  • 8
  • There is no collation in VBA. `CI_AS` stands for case insensitive accent sensitive, so probably replace your `Arr(aRw) < Arr(aRw - 1)` with `StrComp(Arr(aRw), Arr(aRw - 1), vbTextCompare) < 0`. – GSerg Aug 21 '22 at 19:51
  • @GSerg , OK just checked your solution and it's not working, the wildcard character (') is ordered in SQL before Hebrew character and the VBA is count is as bigger then... – xl0911 Aug 21 '22 at 20:18
  • I don't see that happening. `? strcomp("'", chrw$(&h05d0&), vbTextCompare)` => `-1`, hence `'` is ordered before Hebrew characters. – GSerg Aug 21 '22 at 23:40
  • If you want your output data sorted there is one and only way to do that, by adding an `ORDER BY` to your query. – Sean Lange Aug 22 '22 at 04:01
  • @GSerg please see my "edit", the character (') is with additional Hebrew character , and string2 is the opposite the result is wrong. – xl0911 Aug 22 '22 at 05:20
  • `it is clearly a VBA bug` - or, you misunderstand how it is supposed to work. First of all, the [VBA editor is not Unicode](https://stackoverflow.com/a/25260658/11683) and you cannot put "א'" into it with a meaningful result. Then, in an environment with a Unicode editor, such as VB.NET, `String.Compare(STR1, STR2, StringComparison.CurrentCulture)` will return `1` as well, and so will do all other [`StringComparison`](https://docs.microsoft.com/en-us/dotnet/api/system.stringcomparison) modes. The RTL mode applies only when displaying the string. See https://stackoverflow.com/q/57769292/11683. – GSerg Aug 22 '22 at 07:54
  • @GSerg This behavior also occurs in an array without hardcoded in the VBE. I'm not sure I understand, what I need to do in order to get -1 in this scenario when I have Hebrew text with ('). – xl0911 Aug 22 '22 at 14:19
  • As explained in https://stackoverflow.com/questions/57769292/problems-with-parsing-rtl-languages-when-a-string-ends-with-a-direction-agnostic#comment101981110_57769292, you need to parse the string yourself, apply the Unicode bidirectional algorithm to obtain the displayed position of the glyphs, and sort based on that. Which nobody does. SQL Server doesn't do it either and [also thinks that STR1 is bigger than STR2](https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=e6d81e4f955386b52fcebfd27af33a47). – GSerg Aug 22 '22 at 15:32
  • @GSerg if I replace the (א) with English text I get -1, if I replace the (') with other wildcard like (*) for example I get -1, the only combination that return 1 is Hebrew text and ('). I don' want to parse the string, it will take a lot of time on the amount of rows I have, the strange thing is why in your SQL example it thinks STR1 is bigger than STR2 but in my real results I get the opposite ? – xl0911 Aug 23 '22 at 06:55
  • Lexicographical comparison can be funny at times. `strcomp("a", "'", vbTextCompare)` => `1`, `strcomp("a'", "'a", vbTextCompare)` => `-1`. Probably you are doing something wrong in the database, like missing an `N` before a literal or a collation, so the results are wrong to a degree? Can you reproduce your SQL results with dbfiddle? – GSerg Aug 23 '22 at 07:38
  • @GSerg, sorry about the delay, your strcomp example that you called "Funny", why isn't is a bug ? in my real SQL query i'm not using a N because I have a standard query SELECT FROM ORDER BY field name, and still I get wrong order when (') is in then text. to summarize the issue we have a SQL server and VBA wrong order when the (') is in the text. – xl0911 Sep 01 '22 at 12:40
  • `why isn't is a bug` - I'm not an expert on these quirks, but I know there are many. It's certainly not a VBA issue. Have an empty folder and create files in there: `a.txt`, `'.txt`, `a'.txt` and `'a.txt`. Sort by name and see how Explorer groups them. `i'm not using a N because I have a standard query` - but you [should be](https://stackoverflow.com/q/10025032/11683) using `N`. And that is assuming the query comes from a Unicode source (e.g. the SSMS editor) and not hardcoded in VBA. – GSerg Sep 01 '22 at 13:20
  • @GSerg using N is not doing anything to my results; Please open new SQL table and insert these two Hebrew items אברגיל , אברג'ל - SELECT with ORDER BY the table and the order will be wrong. – xl0911 Sep 01 '22 at 14:31

0 Answers0