|Subject||RE: [firebird-support] Sorting strings logical (StrCmpLogicalW) ?|
|Author||Svein Erling Tysvær|
>Hello *Unfortunately, I don't think so. If the text is as ordered as your example indicates, then you can of course do:
>Is there a way to sort string data (varchar) in Firebird by a "natural"
>or "logical" sort order, just like using the Windows API function StrCmpLogicalW  ?
>Sort result of text with numbers should be:
>That is, sorting should treat the digits as numbers rather than text.
ORDER BY CAST(SUBSTRING(MyItem FROM 5 for 2) AS INTEGER)
but you will need to add considerably more logic if you want to sort like the Microsoft example you supply (though it is doable, e.g. you could have a separate field or stored procedure that e.g. contained/returned Item000000003, Item000000010 that you only used for sorting and never displayed).
Here's an EXECUTE BLOCK that does something like this:
execute block returns(MySortField varchar(100))
declare i int = 0;
declare i2 int = 0;
declare s varchar(100) = '';
declare s2 varchar(32) = '';
declare i0 int = 0;
for select myvarchar from test into :s2 do /* MyVarchar (in TEST) is defined as VarChar(32) */
i = 1;
s = '';
while (i <= 32) do
i2 = 0;
while (substring(s2 from i+i2 for 1) between '0' and '9') do
i2 = i2+1;
if (i2 = 0) then
s=s||substring(s2 from i for 1);
if (i2 > 0) then
i0 = 9-i2;
while (i0 > 0) do
s=s||substring(s2 from i for i2);
MySortField = s;
Of course, things become a bit more complex if you want the number to contain decimals and you want this sort order:
(numerically speaking, 23 > 5 if it is before the decimal point, whereas 23 < 5 if it is after)