Re: Sorting alphanumeric column

From: <tammy_gutter_at_yahoo.com>
Date: 2 Jun 2005 12:40:04 -0700
Message-ID: <1117741204.034366.283600@g44g2000cwa.googlegroups.com>

My initial post was not a typo. Sorry if I was not clear enough. Basically, I need to sort the numbers first and then any preceding chars if they should exist. I need to do more testing, but I think the following suggestion works perfectly. BTW, I am using Oracle9i Enterprise Edition Rel 9.2.0.3.0 on AIX platform. Sorry, I am not sure what "top post" means. I will not do in the future if you explain what it means.

I am new to Oracle (prev Sybase/SQL Server) and don't quite know all of its capabilities. Thanks for all your time and suggestions!!!

select *
  from
    (

     select  col ,
          translate(col,'x1234567890.', 'x') b_nonnum,
          to_number( translate( col,'1' ||
translate(col,'x1234567890.', 'x'),'1')) as b_num
     from tam

    )
   order by b_num, b_nonnum

TA2.0	TA	2
UA2.0	UA	2
2.1		2.1
2.3		2.3
3		3
UA4.0	UA	4
UB4.0	UB	4
4		4
4.5		4.5
TB4.8	TB	4.8
TA4.9	TA	4.9
5		5
6		6
UA7.1	UA	7.1
7.1		7.1
TA8.0	TA	8
UA8.0	UA	8
UB8.0	UB	8
8		8
UA9.0	UA	9
9		9
UA10.0	UA	10
10		10
10.5		10.5
13		13

TA2.0	TA	2
UA2.0	UA	2
2.1		2.1
2.3		2.3
3		3
4		4
UB4.0	UB	4
UA4.0	UA	4
4.5		4.5
TB4.8	TB	4.8
TA4.9	TA	4.9
5		5
6		6
7.1		7.1
UA7.1	UA	7.1
8		8
UB8.0	UB	8
UA8.0	UA	8
TA8.0	TA	8
9		9
UA9.0	UA	9
10		10
UA10.0	UA	10
10.5		10.5
13		13

Received on Thu Jun 02 2005 - 14:40:04 CDT