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:]].*')
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