Subject Re: [firebird-support] Sort order with unequal length strings
Author Milan Babuskov
doculus2 wrote:
> 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?

I believe you can write:

order by strlen(column) desc, column


order by cast(column as integer)

although this would raise an error if not all values can be converted to

> I have not defined a character set yet. Something like ASCII or
> win1252 should by OK. Would this make a difference?

Probably not.

Milan Babuskov