OraFAQ Forum: SQL & PL/SQL » TO_CHAR masks for decimal numbers

Home » SQL & PL/SQL » SQL & PL/SQL » TO_CHAR masks for decimal numbers

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

TO_CHAR masks for decimal numbers [message #239382]

Tue, 22 May 2007 04:12

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

Report message to a moderator

Re: TO_CHAR masks for decimal numbers [message #239393 is a reply to message #239382]

Tue, 22 May 2007 04:37

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.

Report message to a moderator

Re: TO_CHAR masks for decimal numbers [message #239396 is a reply to message #239393]

Tue, 22 May 2007 04:43

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'), ',')

Report message to a moderator

Re: TO_CHAR masks for decimal numbers [message #239399 is a reply to message #239396]

Tue, 22 May 2007 04:48

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.

Report message to a moderator

Re: TO_CHAR masks for decimal numbers [message #239454 is a reply to message #239382]

Tue, 22 May 2007 08:08

Bill B
Messages: 1971
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

Report message to a moderator

Previous Topic:	Out mode in functions
Next Topic:	Query needed

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 10:51:33 CST 2024