Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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 :]

asked 24 Dec '19, 10:12

Breck%20Carter's gravatar image

Breck Carter
accept rate: 20%

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...

(27 Dec '19, 07:40) Volker Barth
Replies hidden

Breck, so what is your requirement?

(28 Dec '19, 21:27) Volker Barth

> 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.

(30 Dec '19, 07:34) Breck Carter

This doesn't really help but ICU supports a 'numericOrdering' option that SQL Anywhere, unfortunately, does not expose:

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)' )...

(02 Jan '20, 15:22) John Smirnios
Replies hidden

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

(03 Jan '20, 13:15) Volker Barth

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.

(03 Jan '20, 14:09) John Smirnios
showing 2 of 6 show all flat view

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
  declare l_EndNumeric   integer;
  declare l_EndText      integer;
  declare l_StartNumeric integer;
  declare l_StartText    integer;
  declare l_strResult    long varchar;
  set l_strResult   = '';
     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;
           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;
           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

grant execute on usr.OrderStringByNumeric to usr;
permanent link

answered 27 Dec '19, 08:28

Frank%20Vestjens's gravatar image

Frank Vestjens
accept rate: 21%

edited 30 Dec '19, 02:31



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' );
SELECT * FROM t ORDER BY OrderStringByNumeric ( s, 20 );

'a2a11'   out of order
'a22a111' out of order

(28 Dec '19, 08:56) Breck Carter

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

(30 Dec '19, 02:33) Frank Vestjens
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 24 Dec '19, 10:12

question was seen: 1,111 times

last updated: 03 Jan '20, 14:09