Re: how to sort numbers in varchar2

Yours has also one big drawback,

Original problem was:

"I have a varchar2 column which stores characters as well as numbers in string such as '12345'. "

so if you do TO_NUMBER(col1) and there is a text it will raise an error.

Kenneth Koenraadt wrote in message news:<3f0ad068.448094_at_news.inet.tele.dk>...
> On Tue, 8 Jul 2003 13:09:46 GMT, Brian Peasland
> <oracle_dba_at_remove_spam.peasland.com> wrote:
>
> >I tricked the system into doing this for me by ordering by the size of
> >the column and then by the data in the column. For instance, assume I
> >have TABLE_A as follows:
> >
> >COL_A
> >--------------------
> >123
> >45
> >532
> >133
> >
> >Now, I can "order" these by doing the following:
> >
> >ORA9I SQL> select col_a,length(col_a)
> > 2 from table_a
> > 3 order by length(col_a),col_a;
> >
> >COL_A LENGTH(COL_A)
> >-------------------- -------------
> >45 2
> >123 3
> >133 3
> >532 3
> >
> >Then, I just never displayed the second column in my application.
> >
> >Of course, doing the proper thing and storing the numbers in a number
> >field would be better....
>
> Your solution has one drawback : It can only handle integers. Instead,
> just :
>
> select col_a from table_a order by to_number(col_a);
>
> handles decimals as well.
>
>
> - Kenneth Koenraadt
Received on Wed Jul 09 2003 - 01:33:03 CDT