How do I ORDER BY Windows Numerical Sort?

[Breck_Carter]

Apparently StrCmpLogicalW is used by modern File Explorer implementations to sort file names like this

a 1 a 1.txt
a 2 a 2.txt
a 2 a 11.txt
a 11 a 11.txt
a 22 a 22.txt
a 22 a 111.txt

rather than use the traditional dir /ON and ORDER BY sorting

a 1 a 1.txt
a 11 a 11.txt
a 2 a 11.txt
a 2 a 2.txt
a 22 a 111.txt
a 22 a 22.txt

Sooooo, I would like the freedom to choose the StrCmpLogicalW method for ORDER BY (and dir, if possible).

Any thoughts?

[ insert being lazy meme here :]

[VolkerBarth]

Is that a future product suggestion, or are you asking for a SQL user-defined function that can be used within the ORDER BY clause? - For the latter, have you checked whether StrCmpLogicalW can be used as a native external function? (I don't know whether the database server running as a windows service can access Shlwapi.dll functions...) - And of course StrCmpLogicalW would require wide string arguments, and there is no StrCmpLogicalA single byte char equivalent...

[VolkerBarth]

Breck, so what is your requirement?

[Breck_Carter]

> Is that a future product suggestion, or are you asking for a SQL user-defined function

Those are implementations. I don't have a preferred implementation. And no, I have not investigated StrCmpLogicalW.

My requirement is to be able to choose "Windows Numerical Sort" for ORDER BY, instead of the traditional string sort, when it is appropriate.

[johnsmirnios]

This doesn't really help but ICU supports a 'numericOrdering' option that SQL Anywhere, unfortunately, does not expose: http://userguide.icu-project.org/collation/customization

numericOrdering [numericOrdering off] [numericOrdering on] Turns on or off the UCOL_NUMERIC_COLLATION attribute. If set to on, then sequences of decimal digits (gc=Nd) sort by their numeric value.

If exposed, it might have looked something like SORTKEY( str, 'UCA(numeric=true)' )...

[VolkerBarth]

So it is no option to enhance ICU support in that respect?

[johnsmirnios]

Probably not anytime soon. The support itself isn't likely to be hard to add: the difficulty is in all of the upgrade details (e.g. new database version to prevent old servers from using new databases that might use the syntax, preventing new engines from allowing the syntax in old databases, etc). The SQL procedure provided by 'Frank' is probably the most expedient approach. Or an external procedure if better performance is required.

[fvestjens]

This should do the trick. Maybe you have to add something for the '.' in the string. Use the function in the order by and pass the column needed to sort on as in_strValue and the maximum value of the length of the string in the in_MaxFieldLength parameter.

create or replace function usr.OrderStringByNumeric(in in_strValue long varchar,in in_MaxFieldLength integer)
returns long varchar
begin 
  declare l_EndNumeric   integer;
  declare l_EndText      integer;
  declare l_StartNumeric integer;
  declare l_StartText    integer;
  declare l_strResult    long varchar;
  //
  set l_strResult   = '';
  //
currentloop:  
  loop
     if in_strValue = '' then
        leave currentloop
     end if;
     //
     set l_StartText = patindex('%[A-Z]%',in_strValue);
     //     
     if l_StartText = 1 then
        set l_EndText   = patindex('%[A-Z][0-9-]%',in_strValue);
        if l_EndText  = 0 then
           set l_StrResult = l_strResult || repeat('>',in_MaxFieldLength - l_EndText length(in_strValue) + 1) || substring(in_strValue,l_StartText);
           leave currentloop;
        else       
           set l_StrResult = l_strResult || repeat('>',in_MaxFieldLength - l_EndText + 1) || substring(in_strValue,l_StartText,l_EndText);
           set in_strValue = substring(in_strValue,l_EndText+1);
        end if;
     end if;
     //
     set l_StartNumeric = patindex('%[0-9]%',in_strValue);
     if l_StartNumeric = 1 then
        set l_EndNumeric = patindex('%[0-9][A-Z-]%',in_strValue);
        if l_EndNumeric = 0 then
           set l_StrResult = l_strResult || repeat('*',in_MaxFieldLength - l_EndNumeric length(in_strValue) + 1) || substring(in_strValue,l_StartNumeric);
           leave currentloop;
        else
           set l_StrResult = l_strResult || repeat('*',in_MaxFieldLength - l_EndNumeric + 1) || substring(in_strValue,l_StartNumeric,l_EndNumeric);
           set in_strValue = substring(in_strValue,l_EndNumeric+1);
        end if;
     end if;
  end loop;
  //
  return l_strResult
end;

grant execute on usr.OrderStringByNumeric to usr;

[Breck_Carter]

Alas...

CREATE TABLE t ( s VARCHAR ( 20 ) );
INSERT t VALUES ( 'a11a11' );
INSERT t VALUES ( 'a1a1' );
INSERT t VALUES ( 'a2a11' );
INSERT t VALUES ( 'a2a2' );
INSERT t VALUES ( 'a22a22' );
INSERT t VALUES ( 'a22a111' );
COMMIT;
SELECT * FROM t ORDER BY OrderStringByNumeric ( s, 20 );

s
'a1a1'
'a2a11'   out of order
'a2a2'
'a11a11'
'a22a111' out of order
'a22a22'

[fvestjens]

Looks like I missed a check. See the adjusted script. Now it will do the trick.