Re: how to sort numbers in varchar2

From: Brian Peasland <oracle_dba_at_remove_spam.peasland.com>
Date: Tue, 8 Jul 2003 13:09:46 GMT
Message-ID: <3F0AC29A.4E80CF80@remove_spam.peasland.com>

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....

HTH,
Brian

Susan Lam wrote:
>
> I have a varchar2 column which stores characters as well as numbers in
> string such as '12345'. I would like to sort the column so that it
> orders by the actual number in the number string. However, if I do a
> simple order by, 111 is smaller than 22. Is there a easy way to
> accomplish this in sql instead of doing in programming?
>
> Thanks,
> Susan

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"

Received on Tue Jul 08 2003 - 08:09:46 CDT