I’ve stored numerical data as Character in the database. How can I have it sorted as numbers, not text (i.e. “10” should be greater than “9 “)?

Look at the following example:

SQL>create table alphanum (codes varchar(10));
SQL>insert into alphanum values('A123');
SQL>insert into alphanum values('A234');
SQL>insert into alphanum values('A23');
SQL>insert into alphanum values('A3');
SQL>insert into alphanum values('A1');

SQL>-- Normal order [Numeric Off]
SQL>select * from alphanum order by codes;

CODES
==========
A1
A123
A23
A234
A3

SQL>-- Numeric order [Numeric On]
SQL>create collation numeric from eor using '[Numeric On]';
SQL>select * from alphanum order by codes collate numeric;

CODES
==========
A1
A3
A23
A123
A234