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 :] |
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 - 2
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'
(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
|
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...
Breck, so what is your requirement?
> 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.
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)' )...
So it is no option to enhance ICU support in that respect?
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.