RE: Order By Character Column

From: <Katz.C_at_forces.gc.ca>
Date: Wed, 27 Oct 2004 16:37:23 -0400
Message-Id: <20041027203322.0625282585@mx01.forces.gc.ca>

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_at_il.proquest.com] Sent: Wednesday, October 27, 2004 12:44 PM To: keith_at_etix.com; oracle-l_at_freelists.org 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_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
--
http://www.freelists.org/webpage/oracle-l

Received on Wed Oct 27 2004 - 15:33:01 CDT