to_number is often subject to generate ORA-01722: invalid number.
When I deal with integers, I prefer lpad.
ex: sort pseudo-numeric
select * from t order by lpad(col,20);
It performs well, and do not generate ora-01722. ora-01722 does not mean that I have bad data.
select name,value from v$parameter where name like ‘%pool_size’ order by to_number(value);
This works on my db, but it is bad coding, because to_number could well be executed for other parameters. There is no guarantee that oracle execute the function only when the where is true.
It is also more flexible
ex: first alpha alphabetically, then number numerically
order by translate(col,’~0123456789′,’~’), lpad(col,20)
In 10g, regular expression will ease complex sorts
Thank you so much for this, I had been frustrated by this issue and was elated to find such a simple solution. (Now if I had only found this page yesterday). Thanks again!