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)
Received on Thu Jun 02 2005 - 08:54:52 CDT
