oracle变成数字格式,通过在oracle中添加小数来更改数字格式(Change number format by adding decimal to it in oracle)...

As written your to_number() call is just doing an implicit conversion to a string and then an explicit conversion back to a number, which seems pointless, but I assume you're actually dealing with a value from a varchar2 column. In which case you see:

select to_char(to_number('000005500'),'99.99') from dual

TO_CHA

------

######

You're seeing the hashes because you can't fit your four-digit number, 5500, into a 99.99 format - you have four digits before the decimal point and the format mask only allows for two.

The bit you seem to be missing is dividing by 100 to get the decimal:

select to_char(to_number('000005500') / 100,'99.99') from dual;

TO_CHA

------

55.00

Another approach, if you want to keep it as a string with the same number of leading zeros as the oroginal value, is to leave it as a string, chop it up with substr(), and concatenate the parts back together. Using a CTE as a demo:

with t as (select '000005500' as val from dual)

select val, substr(val, 1, length(val) - 2)

|| '.' || substr(val, length(val) - 1, 2) as adj_val

from t;

VAL ADJ_VAL

--------- ---------------------------------------------

000005500 0000055.00