Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Sorting a VARCHAR field in Numeric Order

james_pJul 28 2008 — edited Jul 28 2008

DB Version:10g Release 2

I know that the Subject line is a bit self contradicting. But here is my requirement. I have a VARCHAR column which stores numbers, but is there any way i could sort values in this column numerically (Ascending)

CREATE TABLE keith
(mykey varchar2(3));

   INSERT INTO keith VALUES ('97');
   INSERT INTO keith VALUES ('98');
   INSERT INTO keith VALUES ('99');
   INSERT INTO keith VALUES ('100');
   INSERT INTO keith VALUES ('101');

Since Oracle is calculating the ASCII values for this VARCHAR column, I can't sort the values numerically(understandably) . This is what i get when i try to sort the column mykey in ASC order

  
   SQL> SELECT * FROM KEITH ORDER BY MYKEY ASC;
   
   MYK
   ---
   100
   101
   97
   98
   99
   

Is there any way i could sort the values Numerically for this VARCHAR column?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 25 2008
Added on Jul 28 2008
7 comments
10,301 views