CODEWITHSUNDEEP
sqloracle-database
Riki
Riki

Reputation: 7

Change number format by adding decimal to it in oracle

I have a number 000005500 and I want it to be in format 0000055.00 or 55.00 using an Oracle select query.

I used this query:

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

but its displaying #####

How can I display it in the format I need?

Upvotes: 0

Views: 3877

Answers (3)

Boneist
Boneist

Reputation: 23578

Another couple of alternatives around the final formatting:

select to_char(to_number('000005500')/100,'0999999D90') leading_zeros,
       to_char(to_number('000005500')/100,'fm9999999D90') no_leading_zeros
from dual;

Upvotes: 0

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

Firstly, 000005500 is not a number. A number doesn't start with zero. You are dealing with a string.

I want it to be in format 0000055.00

You can only expect it to be a string as an output, and not a number.

Anyway, to get the output as 55.00 as NUMBER, you could do the following -

SQL> WITH DATA AS(
  2  SELECT '000005500' num FROM DUAL
  3  )
  4  SELECT to_char(to_number(replace(num,'0','')),'99D99') FROM DATA
  5  /

TO_CHA
------
 55.00

SQL>

Or,

SQL> WITH DATA AS(
  2  SELECT '000005500' num FROM DUAL
  3  )
  4  SELECT to_char(to_number(rtrim(ltrim(num,'0'),'0')),'99D99') FROM DATA
  5  /

TO_CHA
------
 55.00

SQL>

Edit

Alex's method is also nice, it uses simple mathematics to convert it to DECIMAL. I would prefer his way for the first part.

Upvotes: 1

Alex Poole
Alex Poole

Reputation: 191275

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

Upvotes: 4

Related Questions