Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR masks for decimal numbers
TO_CHAR masks for decimal numbers [message #239382] Tue, 22 May 2007 04:12 Go to next message
fida
Messages: 2
Registered: May 2007
Junior Member
Hi all.

I'm having a problem with decimals.
I want to format them with only the necessary number of decimal digits, discarding 0's at the right.

For instance, if we have:

NUMBER
------
0,85
3,092
8,6

i want to have 2 or less decimal digits [0,85 3,09 8,6] (',' is my localized decimal separator)

I've tried with '9990D99' but it gives me -> [0,85 3,09 8,6]

Will I have to play with String functions to achieve it?

Thanks
Re: TO_CHAR masks for decimal numbers [message #239393 is a reply to message #239382] Tue, 22 May 2007 04:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I don't understand.
You say that:
i want to have 2 or less decimal digits [0,85 3,09 8,6]
and then say that the format mask '9990D99' is no good because
it gives me -> [0,85 3,09 8,6]


Now I may be getting on a bit, but I don't see too much difference between what you want and what you've got.
Re: TO_CHAR masks for decimal numbers [message #239396 is a reply to message #239393] Tue, 22 May 2007 04:43 Go to previous messageGo to next message
fida
Messages: 2
Registered: May 2007
Junior Member
Sorry, I wanted to say [0,85 3,09 8,60], whereas i wanted 8,6.

I've come to a solution, but i don't know if it's the best:

RTRIM( RTRIM( LTRIM( TO_CHAR(THE_NUMBER,'99990D99') ), '0'), ',')
Re: TO_CHAR masks for decimal numbers [message #239399 is a reply to message #239396] Tue, 22 May 2007 04:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Use 99990D00 as a format mask:
SQL> with source as (select 0.85  col from dual union all
  2                  select 3.092 col from dual union all
  3                  select 8.6   col from dual)
  4  select to_char(col,'9990D00') from source;

TO_CHAR(
--------
    0.85
    3.09
    8.60

Just be aware that this is now a string, and not a number that you are dealing with.
Re: TO_CHAR masks for decimal numbers [message #239454 is a reply to message #239382] Tue, 22 May 2007 08:08 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
I think he wants to suppress the trailing blanks. Use the fm format mask


  1  with source as (select 0.85  col from dual union all
  2                   select 3.092 col from dual union all
  3                   select 8.6   col from dual)
  4*  select to_char(col,'fm9990D99') from source
whb@xe>/

TO_CHAR(
--------
0.85
3.09
8.6
Previous Topic: Out mode in functions
Next Topic: Query needed
Goto Forum:
  


Current Time: Tue Dec 06 04:22:00 CST 2016

Total time taken to generate the page: 0.15788 seconds