Re: Sorting numbers on a VARCHAR2 field

From: Yong Huang <yhuang_at_indigopool.com>
Date: Tue, 7 Nov 2000 09:34:33 -0600
Message-ID: <8u97k2$rlk$1@news.sinet.slb.com>

Not if you have a number with more digits than some alphabetic words:

SQL> select * from test;

A

---

test
yong
1
132
15
10000

6 rows selected.

SQL> select lpad(a,10,' ') new_a from test order by new_a;

NEW_A

---

         1
        15
       132
      test
      yong
     10000

6 rows selected.

You want 10000 above "test", or see "test" and "yong" to be above 1, right? It's not.

But I can't think of a way to solve the problem, at least not without creating another table for temporary storage.

Yong Huang
yhuang_at_indigopool.com

Greg Wong <gregory.wong_at_clear.co.nz> wrote in message news:01c0484e$2b8ec640$db671eac_at_icwkcheah...
> 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 Tue Nov 07 2000 - 09:34:33 CST