Re: to_char number format with optional decimal-point?

From: <skuzworks_at_googlemail.com>
Date: 21 Jul 2006 02:23:41 -0700
Message-ID: <1153473821.260674.156020@m73g2000cwd.googlegroups.com>

Martin T. wrote:
> G Quesnel wrote:
> > 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 Received on Fri Jul 21 2006 - 04:23:41 CDT