You are now on my old blog. Please update your bookmarks to my new blog
http://laurentschneider.com




13 June 2005

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<1A<1AA<1B<2C<10A:
lpad(regexp_substr(col,'^[[:digit:]]*'),20)||regexp_substr(col,'[^[:digit:]].*')

0 Comments:

Post a Comment

<< Home