Re: Sorting alphanumeric column

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 02 Jun 2005 08:46:51 +0200
Message-ID: <d7ma0p$p52$03$1@news.t-online.com>

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 Received on Thu Jun 02 2005 - 01:46:51 CDT