to_char number format with optional decimal-point?

Martin T. · *nix forums beginner Joined: 07 Mar 2005 Posts: 39

Hello all!

Basically, what I want is the following.
Num -> Str
1 -> '1'
10 -> '10'
1.01 -> '1.01'

Is there a possible Number Format Model that will not display the
decimal if there are no decimal digits?
(I know this is the default behaviour of to_char, but I need aditional
formatting, so I need to spec the format model.)

Related to this: Is it possible to specify a number-format model that
will exactly reproduce the to_char default behaviour?

thanks!

best,
Martin

G Quesnel · *nix forums addict Joined: 29 Apr 2005 Posts: 77

I don't have the answer to your question, but if you can not find the
formating string then you could produce the desired result by adding a
case structure around your to_char formatting. Something like ...
SQL> Select (case when 10.01 > trunc(10.01)
2 then trim(trailing '0' from to_char(10.01,'990.999'))
3 else to_char(10.01,'999') end) A_Number
4 from dual;

A_NUMBER
--------
10.01

or perhaps you could wrap the formatted column in two trim statement:
- first to remove trailing '0'
- second, to remove trailing '.'

Hopefully, someone else can come up with a formating string.

Martin T. · *nix forums beginner Joined: 07 Mar 2005 Posts: 39

G Quesnel wrote:

skuzworks@googlemail.com · *nix forums beginner Joined: 21 Jul 2006 Posts: 1

Martin T. wrote:

Google