RE: Order By Character Column
- From: Katz.C@xxxxxxxxxxxx
- To: Mark.Bobak@xxxxxxxxxxxxxxx, keith@xxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Wed, 27 Oct 2004 16:37:23 -0400
Try this. It's also kludgy but at least its a bit of a smaller kludg.
SQL> select case
2 when ascii(a) < 65 then lpad(to_number(a),8)
3 else a
4 end
5 from sort_test
6 order by 1;
CASEWHEN
--------
1
7
11
20
A
B
6 rows selected.
(Mark, Your create table statement doesn't have (a number) does it?)
chaim
-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak@xxxxxxxxxxxxxxx]
Sent: Wednesday, October 27, 2004 12:44 PM
To: keith@xxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: RE: Order By Character Column
Hmm, it's a little kludgy, but I don't see a more straightforward =
approach.
Try this:
create table sort_test(a number);
insert into sort_Test values('B');
insert into sort_Test values('A');
insert into sort_Test values('20');
insert into sort_Test values('11');
insert into sort_Test values('7');
insert into sort_Test values('1');
commit;
select * from sort test;
create or replace function is_number(in_str varchar2) return number
is
ret_val number;
begin
if translate(in_str,'0123456789','9999999999') =3D =
rpad('9',length(in_str),'9') then
ret_val:=3D 1;
else
ret_val:=3D 0;
end if;
return ret_val;
end;
/
select * from sort_test order by =
decode(is_number(a),1,to_number(a),999999),a;
But, I'd be interested in a more elegant solution, if someone has got =
one.
-Mark
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Keith M Cutler
> Sent: Wednesday, October 27, 2004 11:31 AM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Order By Character Column
>=20
>=20
> 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=20
> letters and
> numbers (as characters). I need to order the output as follows:
> =20
>=20
> 1
>=20
> 2
>=20
> 7
>=20
> 11
>=20
> 20
>=20
> A
>=20
> B
>=20
> =20
>=20
> I've tried using.=20
>=20
> =20
>=20
> decode(instr(translate(row_name,'0123456789ABCDEFGHIJKLMNOPQRS
> TUVWXYZ','0000
> 000000XXXXXXXXXXXXXXXXXXXXXXXXXX'),'X',1),1,row_name,
>=20
> 2,row_name,
>=20
> 3,row_name,
>=20
> 4,row_name,
>=20
> 5,row_name,
>=20
> 6,row_name,
>=20
> 7,row_name,
>=20
> 8,row_name,
>=20
> to_number(row_name))
>=20
> =20
>=20
> but get results.
>=20
> =20
>=20
> 1
>=20
> 11
>=20
> 2
>=20
> 20
>=20
> 7
>=20
> A
>=20
> B
>=20
> =20
>=20
> Anyone have an idea how I can get the specified output?
>=20
> =20
>=20
> Thanks.
>=20
> =20
>=20
> =20
>=20
> Keith M. Cutler
>=20
> Oracle 8i Certified DBA=20
>=20
> keith@xxxxxxxx
>=20
> =20
>=20
> =20
>=20
> =20
>=20
> =20
>=20
>=20
>=20
> --
> //www.freelists.org/webpage/oracle-l
>=20
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
