Re: Sorting numbers on a VARCHAR2 field

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.