Re: Sorting alphanumeric column

From: Maxim <mdemenko_at_gmail.com>
Date: 2 Jun 2005 08:33:35 -0700
Message-ID: <1117726415.126741.34520@o13g2000cwo.googlegroups.com>

DA Morgan schrieb:
> Maxim Demenko wrote:
> > DA Morgan schrieb:
> >
> >> tammy_gutter_at_yahoo.com wrote:
> >>
> >>> Hi,
> >>>
> >>> Is there a way to sort an alphanumeric column in this order?
> >>> Basically, I want to ignore the alpha characters:
> >>>
> >>> .2
> >>> .4
> >>> 2
> >>> 2.05
> >>> 2.22
> >>> 2.4
> >>> 2.6
> >>> TA2.9
> >>> TB2.8
> >>> 3.2
> >>> 4.8
> >>> 7.1
> >>>
> >>> Thanks in advance.
> >>
> >>
> >>
> >> Not easily. TAke out the fact that you wish to sort TA2.9 before TB2.8
> >> and it would be easy.
> >
> >
> > In *this* alphanumeric sequence it would be too ;-)
> >
> > CREATE TABLE a(b VARCHAR2(10));
> > insert into a values('.2');
> > insert into a values('.4');
> > insert into a values('2');
> > insert into a values('2.05');
> > insert into a values('2.22');
> > insert into a values('2.4');
> > insert into a values('2.6');
> > insert into a values('TA2.9');
> > insert into a values('TB2.8');
> > insert into a values('3.2');
> > insert into a values('4.8');
> > insert into a values('7.1');
> >
> > SELECT b FROM a
> > ORDER BY
> > to_number(translate(decode(b,'TB2.8','TA2.9','TA2.9','TB2.8',b),'0123456789.ABCDEFGHIJKLMNOPQRSTUVWXYZ','0123456789,'));
> >
> >
> >
> > Best regards
> >
> > Maxim

>

> You are assuming that you know all of the possible values.
> I was assuming that I didn't: That the sort algorithm must
> be generic.
> --
> Daniel A. Morgan
> http://www.psoug.org
> damorgan_at_x.washington.edu
> (replace x with u to respond)

You are completely right, i was just kidding, sorry. Although , a little bit sense was in my posting too - i meant: if you have a sequence with *predictable* amount of *predictable* exceptions, you can control those exceptions easily with decode.

Best regards

Maxim Received on Thu Jun 02 2005 - 10:33:35 CDT