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.

Learn more about the LPAD function.