How to sort VARCHAR2 field in numerically using ORDER BY

Courtesy : Kiran Bukka

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

Solution : This can be done using LPAD function in the order by clause. LPAD with spaces for the maximum length of the field which
we want to sort by.

This SQL pads the front of the field with spaces up to maximum length of the field. Now, the results should be sorted numerically in ascending order

For Ex : If we want to sort by system_number field in csi_systems_b table. This field is a varchar(30) field.
so in order by clause we have to put it as order by LPAD(csb.system_number,30)

Query : See the difference in the below query result with and without LPAD function.