Reputation: 43
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?
Upvotes: 1
Views: 5030
Reputation: 168806
Use TO_CHAR
with a leading zero in the number format:
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
| COST |
|-----------|
| EUR 0.97 |
| USD 0.87 |
| USD 0 |
| EUR 0.10 |
| EUR 10 |
| USD 12.34 |
Upvotes: 0
Reputation: 95101
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')
Upvotes: 2
Reputation: 9365
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
Upvotes: 1