Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Order By Character Column

Order By Character Column

From: Keith M Cutler <keith_at_etix.com>
Date: Wed, 27 Oct 2004 10:30:47 -0500
Message-Id: <200410271530.i9RFUuW09249@linux19022.dn.net>


I have a report which requires I order the results by a column called row_name. This column is varchar2(8) with values being both letters and numbers (as characters). I need to order the output as follows:  

1

2

7

11

20

A

B  

I've tried using.  

decode(instr(translate(row_name,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ','0000

000000XXXXXXXXXXXXXXXXXXXXXXXXXX'),'X',1),1,row_name,

2,row_name,

3,row_name,

4,row_name,

5,row_name,

6,row_name,

7,row_name,

8,row_name,

to_number(row_name))  

but get results.  

1

11

2

20

7

A

B  

Anyone have an idea how I can get the specified output?  

Thanks.    

Keith M. Cutler

Oracle 8i Certified DBA

keith_at_etix.com        

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Oct 27 2004 - 10:28:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US