--- In firebird-support@yahoogroups.com, "Alan McDonald" <alan@...> wrote:

>
> I have a table with a field value like this:
> 1A
> 1B
> ..
> 2A
> ..
> 9A
> 10A
> 11A
> 11B
>
>
> etc
>
> does anyone know a sneaky way of ordering this column without

resorting to a

> separate sort order column or renaming 1A to 01A etc, such that 1A comes
> first (ie. not after 10A)?

Is it only a single character in the field? If so, you can probably
use something like this, which only requires the built in UDFs.

select *
from foo
order by case when strlen(bar) = 1 then -1 else cast(Substr(bar, 1,
strlen(bar)-1) as Integer) end,
bar

If there are a varied number of Alpha characters, or they are in
varying positions, it could probably be done with enough gymnastics.

(I couldn't get the built in substring command to work with the 'for'
parameter being an expression returned from a UDF).

Adam