No zero before decimal

Question

I'm trying to solve this problem. This sql

select t.currency || ' ' || abs(t.price) 
from apple_month t
where t.price <= 1

(t.price is a float)

returns number like this:

EUR ,97
USD ,87

There are no zero digits before the decimal. What is wrong?

you could use the `to_char` to format the number returned by abs — SomeJavaGuy, Sep 23 '15 at 10:28
possible duplicate of [Remove Trailing decimal zero](http://stackoverflow.com/questions/18839798/remove-trailing-decimal-zero) — MT0, Sep 23 '15 at 11:34

score 2 · Accepted Answer · answered Sep 23 '15 at 10:38

2

The symbol || concatenates strings. In

t.currency || ' ' || abs(t.price)

you rely hence on the implicit conversion of abs(t.price) to string. And this converts the value 0.97 to ',97'.

Use TO_CHAR to format your number explicitely. E.g.

t.currency || ' ' || to_char(abs(t.price), 'fm9999990d00')

answered Sep 23 '15 at 10:38

Thorsten Kettner

89,309
7
49
73

it works but for integer as 0 i want that returns 0 not 0.00 – Stefano Carboni Sep 23 '15 at 10:55
Okay, I thought you wanted all numbers with two digits after the comma. If not then use 9s. And as there is no format for optional decimal separators you must even trim them: `rtrim(to_char(abs(t.price), 'fm9999990d99'), '.,')`. Or use `CASE` to check whether you have an integer or a decimal number. – Thorsten Kettner Sep 23 '15 at 11:22

score 1 · Answer 2 · answered Sep 23 '15 at 10:34

1

If you want to add zero before decimal if the value is less than 1 then,
try;

select t.currency || 
    case when abs(t.price) < 1 then ' 0' || abs(t.price) 
    else ' ' || abs(t.price) end 
from apple_month t
where t.prezzo <= 1

answered Sep 23 '15 at 10:34

Praveen

8,945
4
31
49

MT0 · Answer 3 · 2015-09-23T11:33:00.840

Use TO_CHAR with a leading zero in the number format:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE APPLE_MONTH( CURRENCY, PRICE, PREZZO ) AS
          SELECT 'EUR',   0.97, 1 FROM DUAL
UNION ALL SELECT 'USD',   0.87, 1 FROM DUAL
UNION ALL SELECT 'USD',   0,    1 FROM DUAL
UNION ALL SELECT 'EUR',  -0.1,  1 FROM DUAL
UNION ALL SELECT 'EUR',  10,    1 FROM DUAL
UNION ALL SELECT 'USD', -12.34, 1 FROM DUAL;

Query 1:

SELECT CASE WHEN TRUNC( price ) = price
            THEN currency || ' ' || TRIM( TO_CHAR( abs(price), '9999990' ) )
            ELSE currency || ' ' || TRIM( TO_CHAR( abs(price), '9999990d00' ) )
            END AS COST
FROM   apple_month
WHERE  prezzo <= 1

Results:

|      COST |
|-----------|
|  EUR 0.97 |
|  USD 0.87 |
|     USD 0 |
|  EUR 0.10 |
|    EUR 10 |
| USD 12.34 |

it works but i don't want .oo after 0. When price is 0 i want 0, not 0.00 — Stefano Carboni, Sep 23 '15 at 10:53

No zero before decimal

3 Answers3