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?