Subject Re: [firebird-support] Sort order with unequal length strings
Author Woody
From: "doculus2" <doculus2@...>
> 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 have not defined a character set yet. Something like ASCII or
> win1252 should by OK. Would this make a difference?
> I am using FB1.5.3 on W2000 pro.

I solve this by padding all numbers with leading 0's (zeros) up to a certain
length. For instance, '999' becomes '00000999'. How many zeros depends on
your estimate of what the largest number would be. I use my own routines in
code for adding/removing the leading zeros when working with the fields.

Woody (TMW)