Re: Sorting numbers on a VARCHAR2 field

This seems to work. Kind of looks funny with the extra padding in the front but it will have to do.

Cheers.

David Fitzjarrell <oratune_at_aol.com> wrote in article <8u78qn$et$1_at_nnrp1.deja.com>...
> In our last gripping episode "Greg Wong" <gregory.wong_at_clear.co.nz>
> wrote:
> > Hmmm... I've tried all suggestions using LPAD, TO_NUMBER, and LENGTH
> > functions and they were not exactly what I wanted. Maybe I need to
 clarify
> > what a want a bit better.
> >
> > I have a table that has a field which is of a VARCHAR2 datatype. In
 this
> > field I have alphanumeric values has well as numeric values.
> > I want to sort this field so that instead of appearing like 1, 10,
 11, 2,
> > 20, 21 I want it to sort like 1, 2,10,11,20,21.
> >
> > Thanks for you help in advance.
> >
> > Greg Wong <gregory.wong_at_clear.co.nz> wrote in article
> > <01c04789$960e8700$db671eac_at_icwkcheah>...
> > > Hi,
> > >
> > > I want to sort a field that contains numbers but when I do a order
 by in
> > > the SQL query it sorts them by their ASCII values rather than their
 face
> > > value..ie the numbers appear in this order : 1,10,11,2,20,21
> > >
> > > I could can the field's datatype to a number but some of the values
 in
 this
> > > field are also alphanumeric.
> > >
> > > Is there a way to get around this???
> > >
> >
>
> Let us try this route:
>
> column keyfld2 noprint
>
> select keyfld, lpad(keyfld, 10, ' ') keyfld2, valfld
> from sorttst
> order by keyfld2
> /
>
> We obtain:
>
>
> KEYFLD VALFLD
> ---------- ----------------------------------------
> 1 First one
> 2 Second one
> 3 Another one
> 4 Another one
> 7 Another one
> 10 Another one
> 11 Another one
> 14 Another one
> 17 Another one
> 20 Another one
> 22 Another one
>
> KEYFLD VALFLD
> ---------- ----------------------------------------
> 23 Another one
> 40 Another one
> 57 Another one
> 78 Another one
>
> Is this more of what you had in mind?
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Received on Mon Nov 06 2000 - 18:00:50 CST