Re: Sorting numbers on a VARCHAR2 field

Hi-

if the column contains alpha-nuemeric values (1 2 3..., a b c...) then your suggested query will produce an error as follows:

ERROR:
ORA-01722: invalid number

Thanks

In article <3a06b690.21289913_at_news.fr.uu.net>,   y.kermabon_at_tegelog.fr (Yannick Kermabon) wrote:
> Hi
>
> You can do this :
>
> select *
> from mytable
> order by to_number(varchar_column);
>
> hth
> Yannick
>
> On Mon, 06 Nov 2000 00:36:47 GMT, "Greg Wong"
> <gregory.wong_at_clear.co.nz> wrote:
>
> >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???
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Nov 06 2000 - 10:11:10 CST