Oracle Sorting Alphanumeric Values

Posted on by

While Doing sort in oracle the Normal sort works differently for the following Data

CREATE TABLE TestTable(Col1 VARCHAR2(20));

insert into TestTable values('A11');
insert into TestTable values('A260');
insert into TestTable values('A10');
insert into TestTable values('A5');

A11
A260
A10
A5

  SELECT * 
    FROM TestTable 
   ORDER BY col1;

Output

A10
A11
A260
A5

You may expected A5 before A10 and A11. But It would have been before A10 and A11 when it is A05. The workaround for this is as below.

SELECT * 
  FROM TestTable
 ORDER BY lpad(col1,9999999);

Output

A5
A10
A11
A260

Comments are closed.