Order By Character Column
- From: "Keith M Cutler" <keith@xxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 27 Oct 2004 10:30:47 -0500
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@xxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
