CODEWITHSUNDEEP
sqloracle-database
Stefano Carboni
Stefano Carboni

Reputation: 43

No zero before decimal

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

Answers (3)

MT0
MT0

Reputation: 168806

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 |

Upvotes: 0

Thorsten Kettner
Thorsten Kettner

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

Praveen
Praveen

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

Related Questions