order by to_number ?

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);
1
2
10

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)
a
aa
z
2
3
11

In 10g, regular expression will ease complex sorts

1

Published by

Laurent Schneider

Oracle Certified Master

One thought on “order by to_number ?”

  1. 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!

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>