> I have a varchar(12) column with numbers stored as strings. The
> Paradox app previously used had no trouble with '999' coming before
> '1000'. If I store the first number as ' 999' [an explicit space
> before the 999] then the sort is OK.
> What I need is a SELECT ... ORDER BY ... to return numbers first, in
> numerical order, then any entries starting with A..Z, in alphabetical
> order. This implies that a shorter string always sorts before a longer
> one. Is this contrary to the design of Firebird?

It is contrary to common logic, not just Firebird design. I would say
that Paradox is on its own there. It is like saying that 'Zebra' comes
before 'Antelope' because it is shorter.

If the field is indeed a varchar, and in the absence of other
charactersets and collations, sorting is pretty simple.

Assuming the two values:

999
1000

Does '1' come before '9', well yes CHR(49) is smaller than CHR(57),
and therefore a string starting with '1' is defined to be smaller than
a string starting with '9'.

The problem is that you wish it to sort numerically not
alphabetically. It is pretty simple to do this by casting the field in
the order by clause as an integer. The downside to this is that it
will not work if your field also contains strings that can not be
converted to an integer. Unfortunately, the ordering by string length
first function won't work either. It will not give you an exception
like casting, but you will end up with 'Zebra' before 'Antelope'.

Maybe you can create a custom collation, I have never done that though.

Adam