Seeking Explanation of Comparer.OrdinalIgnoreCase

[smpa01]

I am trying to understand how comparer-ordinalignorecase works. I have run the following test.

= #table({"col1"},{
{Comparer.OrdinalIgnoreCase("Abc", "abc")}/*row#=1*/,{Comparer.OrdinalIgnoreCase("Abc", "cab")}/*row#==2*/,{Comparer.OrdinalIgnoreCase("Abc", "xya")}/*row#==3*/,
{Comparer.OrdinalIgnoreCase("Abc", "efg")}/*row#==4*/,{Comparer.OrdinalIgnoreCase("Abc", "")}/*row#==5*/,{Comparer.OrdinalIgnoreCase("Abc", "0")}/*row#==6*/,
{Comparer.OrdinalIgnoreCase("Abc", "ABC")}/*row#==7*/,{Comparer.OrdinalIgnoreCase("Abc", "unknown")}/*row#==8*/,{Comparer.OrdinalIgnoreCase("Abc", "#")}/*row#==9*/
})

I understand that they return 0 on the exact match (row#1 and 7) but how do I know when they return -1 and +1? I can't seem to understand what it returns in what circumstances.

The official doc says the following but what exactly are Ordinal rules to compare the provided values x and y.

I want to utilize this in production, once I fully understand the behaviour.

I expected this to return 0 for row 1 and row 7 and a different value (either 1 or -1) for other rows. But why does it return 3 values? Are there more values that can be returned by the function that I currently don't know of?

@ImkeF @AlexisOlson 

Thank you in advance.

[AlexisOlson · Accepted solution]

The word "ordinal" usually means there is some sort of order involved. For any two objects A and B, there are three possible options when you compare them: A > B, A = B, A < B which correspond to 1, 0, and -1 for this comparer.

You can achieve the same thing as follows:

let
  Source = #table(
    type table [col1 = text, col2 = text], 
    {
      {"Abc", "abc"},     /*row#==1*/
      {"Abc", "cab"},     /*row#==2*/
      {"Abc", "xya"},     /*row#==3*/
      {"Abc", "efg"},     /*row#==4*/
      {"Abc", ""},        /*row#==5*/
      {"Abc", "0"},       /*row#==6*/
      {"Abc", "ABC"},     /*row#==7*/
      {"Abc", "unknown"}, /*row#==8*/
      {"Abc", "#"}        /*row#==9*/           
    }
  ),
    #"Added Custom" = Table.AddColumn(Source, "Compare", each
      if      Text.Lower([col1]) > Text.Lower([col2]) then  1
      else if Text.Lower([col1]) < Text.Lower([col2]) then -1
      else 0
    ,Int64.Type)
in
  #"Added Custom"

The ordering is alphanumeric ignoring case.