Re: Alphanumeric Sort

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 25 Aug 1999 12:48:19 GMT
Message-ID: <7q0omj$l61$3@news.seed.net.tw>

<johnp_at_1rent.com> wrote in message news:7pt94a$aij$1_at_nnrp1.deja.com...
> Oracle Gurus,
>
> I am trying to sort on an alphanumeric column. The default order is:
>
> 1
> 100
> 11
> 2
> 23
> 7
> A1
> A100
> A2
> B2
>
> The desired order is:
>
> 1
> 2
> 7
> 11
> 23
> 100
> A1
> A2
> A100
> B2
>
> Any suggestions?
>
> Thanks!!!

It's easy and can be done in several ways. Suppose your column values contain only alphabets and digits, no period and space. The following is the most elegant form:

select * from table_name
order by translate(col, ' 0123456789', ' '), col Received on Wed Aug 25 1999 - 07:48:19 CDT