Subject Re: Sort Order Query
Author Adam
--- In, "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,

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).