I have a number want to format it as percentage in Presto. I want to format 0.18932 as 18.93%.
I tried:
format('%s%%', decimal_number*100)
format('%s%%', round((a.decimal_number*100), 2))
Here is the result: none formatted: 0.019435618 first method: 1.9435618000% 2nd method: 1.9400000000%
Any easier way to format it as 1.94%
The format function accepts any of the Java format string specifiers:
presto> select format('%.2f%%', 0.18932 * 100);
_col0
--------
18.93%
(1 row)
For those of you who, like me, came here looking to round a decimal or double field down to n decimal places but didn't care about the % sign, you have a few other options than Martin's answer.
If you want the output to be type double then truncate(x,n) will do this. However, it requires x to be type decimal.
select truncate( cast( 1.23456789 as decimal(10,2)) , 2); -> 1.23 <DOUBLE>
If you feel that the truncate is kind of useless here since the decimal(10,2) is really doing the work, then you're not alone. An arguably equivalent transformation would be.
select cast( cast( 1.23456789 as decimal(10,2)) as double); -> 1.23 <DOUBLE>
Is this syntactically better or more performant than truncate(), I have no idea, I guess you get to choose.
And if you don't give a hoot about what type results from the transformation, then I suppose the below is the most direct method.
cast( 1.23456789 as decimal(10,2);