Oracle/PLSQL: Sort a varchar2 field as a numeric field

Question: I have a field defined in Oracle as a varchar2, but it contains numbers. When I use an "order by" clause, the records are sorted ascending by character. But I want to sort it numerically without changing the datatype from varchar2 to numeric. Is there any solution?




Answer: To sort your field numerically, there are two possible solutions:

Solution #1



This first solution only works if the varchar2 field contains all numbers. To do this, you will need to add another field to your "order by" clause that evaluates to a number, but you don't have to include this new field in your "select" portion of the SQL.



For example,

You may have a supplier table defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



When you perform the following select statement, the supplier_id field sorts alphabetically, even though it contains numbers.



select * from supplier

order by supplier_id;



However, you could execute the following SQL, which will return a numerically sorted supplier_id:



select * from supplier

order by to_number(supplier_id);



This SQL converts the supplier_id field to a numeric value and then sorts the value in ascending order. This solution returns an error if not all of the values in the supplier_id field are numeric.

Solution #2 (more eloquent solution)



We'd like to thank Kamil for suggesting this solution.



This solution will work even if the varchar2 field contains non-numeric values.



Again, we'll demonstrate this solution on the supplier table, defined as:



create table supplier

(

supplier_id varchar2(10) not null,

supplier_name varchar2(60)

);



Remember that our goal is to sort the supplier_id field in ascending order (based on its numeric value). To do this, try using the LPAD function.



For example,



select * from supplier

order by lpad(supplier_id, 10);



This SQL pads the front of the supplier_id field with spaces up to 10 characters. Now, your results should be sorted numerically in ascending order.



Please note that if your numbers in the supplier_id field are longer than 10 digits, you may need to increase the second parameter on the LPAD function.