CODEWITHSUNDEEP
sqloracle-database
jonny
jonny

Reputation: 797

Oracle number to char leading 0

I have a function that calculates number of days select (x * 0,5) from dual. my problem is tha when x=1 i get .5 instead of 0,5.What i want to get is numbers in format 0.5,1,1.5,2,2.5 .. Could you please give me a help with this.

Upvotes: 1

Views: 5303

Answers (3)

atamata
atamata

Reputation: 1077

I know this is old but I did the following, where x is datatype NUMBER(20,3)

CASE WHEN x < 1 THEN '0'
  ELSE '' END
  || to_char(x)

I didn't want any trailing zeros but did want a leading zero if x was, say, 0.99 or 0.5

So I'm just using the regular to_char and adding a leading zero if x is less than 1.

Upvotes: 2

rsutti
rsutti

Reputation: 13

If you always want 1 decimal place use to_char to round

select TO_CHAR(28.324, '90.9') from dual;

returns

28.3

and

select TO_CHAR(28, '90.9') from dual;

returns

28.0

Hope this helps

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

Try to use:

select TO_CHAR(x * 0,5, '990,99') from dual

EDIT:

Try to create a function like this:

create function getDecimalFormat(x in number) 
    return varchar2
as
begin
    return RTRIM(TO_CHAR(x, 'FM999999999999990.99'), '.');
end;

Upvotes: 1

Related Questions