trino sql convert double to varchar without scientific notation E

Question

Using Athena version engine 3:

I'm trying to convert double precision value to varchar without E notation.

Currently:

select 
    format('%s', round(abs(0.972) * pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))), 0) / pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))));

This will return 0.97 as expected.

But this format() function is not supported in Athena.

So I had to cast to varchar:

select 
    cast(round(abs(0.972) * pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))), 0) / pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))) as varchar);

This outputs: 9.7E-1.

Is there any alternative to format()? How can I fix this?

Answer 1

In Athena, you can use printf function.

SELECT
  printf('%.2f', round(abs(0.972) * pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))), 0) / pow(10, 2 - ceil(ln(abs(0.972)) / ln(10))))) AS val;