Re: how to sort numbers in varchar2

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