Re: "Absoulte" sorting?

In article <D886zD.MLA_at_dorsai.org> vkwan_at_news.dorsai.org (Vito Kwan) writes:

> I have a question about Oracle sorting. Say I have a colume with
> varchar2 data type, and there are 3 rows under it. They are
> abc11
> abc2
> abc123
>
> After sorting, Oracle gives
> abc11
> abc123
> abc2
>
> Is there a way to sort the colume and makes it
> abc2
> abc11
> abc123
>
> I know I can solve the problem by putting the leading 0, but unfortunately,
> our clients don't want that.
> Any help will be appreciated.

You are actually trying to sort by a value which is derived from the column, sorting the alpha part and the numeric part separately. To achieve that you can either create a dummy sorting column, populated through database triggers to ensure integrity, and sort by the dummy column, or you can use a complex order by clause using the substr() function provided that the alpha part of the alphanumeric column is constant width.

--
"And this is all I have to say about that..."   - F. Gump
      ___
     (o o)
+-oo0-\_/-0oo---------------------------------------------------------------+
|  Alvin W. Law ..... Oracle Corporation ....... Email: alaw_at_us.oracle.com  |
+---------------------------------------------------------------------------+

ORA-03113: end-of-file on communication channel