to_char number format with optional decimal-point?

[Martin T.]

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]

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.]

Post by G Quesnel
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 ...
<snip>
Hopefully, someone else can come up with a formating string.

Thanks for your tip! Seems a pretty clean solution.

Let me state something that may spark some more response ;) ...

1.) It is *not* possible with the ORACLE to_char function (in version
9i2) to specify a number format model that will reproduce the default
behaviour of the function w/o format model.
2.) It is, specifically, *not* possible to describe a number format
model such that the decimal-point is not displayed if there are no
decimals and is displayed if there are significant decimals.

Now - I do also *not* claim that this is the absolute truth, but it
(sadly) will remain my truth until someone proves me wrong.

best,
Martin

p.s.: I guess in 10g there would be some really nice solution with a
regexp :)

[s***@googlemail.com]

Post by Martin T.

Post by G Quesnel
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 ...
<snip>
Hopefully, someone else can come up with a formating string.

Thanks for your tip! Seems a pretty clean solution.
Let me state something that may spark some more response ;) ...
1.) It is *not* possible with the ORACLE to_char function (in version
9i2) to specify a number format model that will reproduce the default
behaviour of the function w/o format model.
2.) It is, specifically, *not* possible to describe a number format
model such that the decimal-point is not displayed if there are no
decimals and is displayed if there are significant decimals.
Now - I do also *not* claim that this is the absolute truth, but it
(sadly) will remain my truth until someone proves me wrong.
best,
Martin
p.s.: I guess in 10g there would be some really nice solution with a
regexp :)

Hello,

Just a thought: does CAST solve this problem?

SQL> select cast (1 as varchar(30)) from dual;

CAST(1ASVARCHAR(30))
------------------------------
1

SQL> select cast (100 as varchar2(30)) from dual;

CAST(100ASVARCHAR2(30))
------------------------------
100

SQL> select cast (1.1001 as varchar2(30)) from dual;

CAST(1.1001ASVARCHAR2(30))
------------------------------
1.1001

SQL> select cast (.100 as varchar2(30)) from dual;

CAST(.100ASVARCHAR2(30))
------------------------------
.1

SQL> select cast (1.100 as varchar2(30)) from dual;

CAST(1.100ASVARCHAR2(30))
------------------------------
1.1

SQL> select cast (1.000 as varchar2(30)) from dual;

CAST(1.000ASVARCHAR2(30))
------------------------------
1

[Martin T.]

<snip>

Post by s***@googlemail.com
Hello,
Just a thought: does CAST solve this problem?
...

Hello.

Well, I assume cast(x as varchar2) works pretty much the same as
to_char(x).
So, no, CAST does not solve my problem :)

thanks anyway!

best,
Martin

[Martin T.]

Post by Martin T.

Post by G Quesnel
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 ...
<snip>
Hopefully, someone else can come up with a formating string.

Thanks for your tip! Seems a pretty clean solution.
Let me state something that may spark some more response ;) ...
1.) It is *not* possible with the ORACLE to_char function (in version
9i2) to specify a number format model that will reproduce the default
behaviour of the function w/o format model.
2.) It is, specifically, *not* possible to describe a number format
model such that the decimal-point is not displayed if there are no
decimals and is displayed if there are significant decimals.

1 = wrong
2 = wrong
:-)

So ... finally stumbled upon the default format (why do the docs not
state this? grml ...)

Default format for to_char seems to be 'TM9'

"proove" :) ...
declare
v_num NUMBER:= 0;
begin
LOOP
v_num := v_num + 0.1;
dbms_output.put_line('Num: ' || to_char(v_num) || ' ... explicit: '
|| to_char(v_num, 'TM9'));
EXIT WHEN v_num > 2;
END LOOP;
end;

see also
http://forums.oracle.com/forums/thread.jspa?messageID=714945&#714945

best,
Martin

[Frank van Bortel]

Post by Martin T.
So ... finally stumbled upon the default format (why do the docs not
state this? grml ...)

TM TM The text minimum number format model returns (in decimal output)
the smallest number of characters possible. This element is case
insensitive.
The default is TM9, which returns the number in fixed notation unless
the output exceeds 64 characters. If the output exceeds 64 characters,
then Oracle Database automatically returns the number in scientific
notation.
Restrictions:

You cannot precede this element with any other element.

You can follow this element only with one 9 or one E (or e), but not
with any
combination of these. The following statement returns an error:

SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;

Pg 2-73 of the SQL Reference Manual :)

[Martin T.]

Post by Frank van Bortel

Post by Martin T.
So ... finally stumbled upon the default format (why do the docs not
state this? grml ...)

TM TM The text minimum number format model returns (in decimal output)
the smallest number of characters possible. This element is case
insensitive.
The default is TM9, which returns the number in fixed notation unless
(snipped rest of doc)
Pg 2-73 of the SQL Reference Manual :)

*I admit! I admit!*
Found it 30 minutes after posting the above statement.
But I couldn't bear correcting me twice in the same thread ;)

(But really, I overlooked it twice before ... grml :)

best,
Martin