RE: Order By Character Column

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 27 Oct 2004 12:43:33 -0400
Message-ID: <AA29A27627F842409E1D18FB19CDCF275A9B66@AABO-EXCHANGE02.bos.il.pqe>

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_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Keith M Cutler
> Sent: Wednesday, October 27, 2004 11:31 AM
> To: oracle-l_at_freelists.org
> 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_at_etix.com

>=20

> =20

>=20

> =20

>=20

> =20

>=20

> =20

>=20
>=20
>=20

> --
> http://www.freelists.org/webpage/oracle-l

>=20

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 27 2004 - 11:39:14 CDT