Re: Alphanumeric Sort

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Tue, 24 Aug 1999 10:36:44 +0200
Message-ID: <7ptlk1$3ck$1@oceanite.cybercable.fr>

Assuming that the form of the values of the column is zero or more letters followed by 1 or more digits, here's a query that make your order:

select * from t
order by
decode(instr(translate(col,'0123456789','9999999999'),'9'),

       1, ' ',
       substr(col,1,instr(translate(col,'0123456789','9999999999'),'9')-1)),

to_number(substr(col,instr(translate(col,'0123456789','9999999999'),'9'))) ;

johnp_at_1rent.com a écrit dans le message <7pt94a$aij$1_at_nnrp1.deja.com>...
>Oracle Gurus,
>
>I am trying to sort on an alphanumeric column. The default order is:
>
>1
>100
>11
>2
>23
>7
>A1
>A100
>A2
>B2
>
>The desired order is:
>
>1
>2
>7
>11
>23
>100
>A1
>A2
>A100
>B2
>
>Any suggestions?
>
>Thanks!!!
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Tue Aug 24 1999 - 03:36:44 CDT