CODEWITHSUNDEEP
sqloracle-database
Shurov
Shurov

Reputation: 401

Format number to show at least 1 decimal

I have numbers with different amount of decimals - it may be exactly 100 or 100.65468151. What I need - is to show it with at least 1 decimal (or more). So that if it is exactly 100 - it should be shown as 100.0. While 100.65468151 should be shown with all the available decimals.

How can I do this? P.S. it may be converted to char if needed.

Upvotes: 0

Views: 1190

Answers (1)

Alex Poole
Alex Poole

Reputation: 191570

This seems like an odd requirements, but you can use to_char() with a suitable format model; just using `999.099999999' wouldn't quite work though:

with t (n) as (
  select 100 from dual
  union all select 100.0 from dual
  union all select 100.6 from dual
  union all select 100.654 from dual
  union all select 100.65468151 from dual
)
select n, to_char(n, '999999999.09999999') as text
from t;

         N TEXT               
---------- -------------------
       100        100.00000000
       100        100.00000000
     100.6        100.60000000
   100.654        100.65400000
100.654682        100.65468151

But if you add the FM format modifier then extra trailing zeros won't be included:

with t (n) as (
  select 100 from dual
  union all select 100.0 from dual
  union all select 100.6 from dual
  union all select 100.654 from dual
  union all select 100.65468151 from dual
)
select n, to_char(n, 'FM999999999.09999999') as text
from t;

         N TEXT               
---------- -------------------
       100 100.0              
       100 100.0              
     100.6 100.6              
   100.654 100.654            
100.654682 100.65468151

You need an appropriate number of 9s before and after the decimal point so all possible values can be rendered of course; and you may prefer to make the final 9 before the decimal point a zero. You might also want to consider using the D format element instead of a period . so it honours session NLS settings.

Or, let your presentation layer (application, reporting tool or whatever) do the formatting. You should leave it as an actual number until the last possible moment.

Upvotes: 2

Related Questions