Re: Sorting numbers on a VARCHAR2 field

Try to improve the query of y.kermabon_at_tegelog.fr (Yannick Kermabon) by:

select *
from mytable
where varchar_column like ‘%[0-9]%’
order by to_number(varchar_column);

I hope it will work if numbers are integers in decimal format.

oracle4444_at_my-deja.com wrote:

> 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 - 16:25:13 CST