RE: Order By Character Column

  • From: "Ian Cary" <Ian.Cary@xxxxxxxxxxxxxx>
  • To: Katz.C@xxxxxxxxxxxx
  • Date: Fri, 29 Oct 2004 13:00:06 +0100

This seems to work (using the varchar :) definition of Mark's table) -
although I am assuming that values like '......' and '1.1.1' aren't allowed

Cheers,

Ian


select * from sort_test;

A
----------
B
A
20
11
7
7.3
70

select a
from sort_test
order by
decode(rtrim(translate(a,'0123456789.','00000000000'),'0'),null,to_number(a)),a;

A
----------
7
7.3
11
20
70
A
B




For the latest data on the economy and society 
consult National Statistics at http://www.statistics.gov.uk

**********************************************************************
Please Note:  Incoming and outgoing email messages
are routinely monitored for compliance with our policy
on the use of electronic communications
**********************************************************************
Legal Disclaimer  :  Any views expressed by
the sender of this message are not necessarily
those of the Office for National Statistics
**********************************************************************

The original of this email was scanned for viruses by the Government Secure 
Intranet (GSi) virus scanning service supplied exclusively by Energis in 
partnership with MessageLabs.

On leaving the GSi this email was certified virus-free
--
//www.freelists.org/webpage/oracle-l

Other related posts: