Re: Sorting numbers on a VARCHAR2 field

From: Greg Wong <gregory.wong_at_clear.co.nz>
Date: Mon, 06 Nov 2000 20:49:53 GMT
Message-ID: <01c04833$0b3616c0$db671eac@icwkcheah>

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???
>
Received on Mon Nov 06 2000 - 14:49:53 CST