>Hello *
>
>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 [1] ?
>
>Sort result of text with numbers should be:
>
>Item1
>Item2
>Item3
>Item10
>Item11
>Item20
>Item30
>
>That is, sorting should treat the digits as numbers rather than text.
>
>[1]
> http://msdn.microsoft.com/en-us/library/bb759947.aspx
Unfortunately, I don't think so. If the text is as ordered as your example
indicates, then you can of course do:
SELECT MyItem
FROM Items
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))
as
declare i int = 0;
declare i2 int = 0;
declare s varchar(100) = '';
declare s2 varchar(32) = '';
declare i0 int = 0;
begin
for select myvarchar from test into :s2 do /* MyVarchar (in TEST) is defined
as VarChar(32) */
begin
i = 1;
s = '';
while (i <= 32) do
begin
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
begin
i0 = 9-i2;
while (i0 > 0) do
begin
s=s||'0';
i0=i0-1;
end
s=s||substring(s2 from i for i2);
i=i+i2-1;
end
i=i+1;
end
MySortField = s;
Suspend;
end
end
Of course, things become a bit more complex if you want the number to contain
decimals and you want this sort order:
Item1.23
Item1.5
Item5.1
Item23.1
(numerically speaking, 23 > 5 if it is before the decimal point, whereas 23 < 5
if it is after)
HTH,
Set
