RE: How can I do this better/best

From: Anthony Molinaro <amolinaro_at_wgen.net>
Date: Thu, 17 Mar 2005 10:09:53 -0500
Message-ID: <D17DB304A9F42B4787B68861F9DAE61CE8EE3B@wgdc02.wgenhq.net>

David,
  Not sure if it's "best", but easiest is to just   Play with the formatting (this way you don't   Have to look for the decimal):

SQL> select to_char(9,'fm9999999.00')||'%' from dual;

TO_CHAR(9,'F

---

9.00%

SQL> select to_char(10,'fm9999999.00')||'%' from dual;

TO_CHAR(10,'

---

10.00%

SQL> select to_char(375,'fm9999999.00')||'%' from dual;

TO_CHAR(375,

---

375.00%

SQL> select to_char(375.99,'fm9999999.00')||'%' from dual;

TO_CHAR(375.

---

375.99%

hope that helps,
  Anthony

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Sent: Thursday, March 17, 2005 10:00 AM
To: oracle-l_at_freelists.org
Subject: How can I do this better/best

SELECT
x.*,
CASE
    WHEN c =3D 0 THEN lpad(x||'.0', 5, ' ')     WHEN c > 0 THEN lpad(x, 5, ' ')
    ELSE NULL
END || '%' output_var
FROM (

     SELECT
     99.9 x,
     instr( 99.9, '.', 1, 1) c
     from dual

) x;

Takes in 1 argument (here I have it hardcoded as 99.9, but it would need to
be a bind variable) Outputs a varchar2 (output_var)

I couldn't figure it out.

Here is what it does:

         X C OUTPUT_VAR
---------- ---------- ----------------

         1          0   1.0%
        11          0  11.0%
       100          0 100.0%
      15.9          3  15.9%
       8.4          2   8.4%

So I can pass in any number and output a properly formatted number with 1
decimal place and a percent sign.

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 17 2005 - 10:13:21 CST