Casting Decimal to Currency in AWS Athena

I have a field value(decimal(23, 2)) and I am querying over it like this:

sum(value) and as output i get 1200000.32

I am trying to cast it to currency to get $1.200.000,32 like this:

cast(sum(value) as money) == FAIL
sum(value::money) == FAIL
cast(sum(gmv::numeric) as money) == FAIL

How can I get the desired currency type?

Answer

There is no money type in presto. And Amazon’s version of presto does not support format and I could not find any buld in function to format numbers. So you will need to either create and use some user defined function to do this, or use some replacing and regular expressions magic:

WITH dataset(d) AS (
   VALUES 
  (decimal '1'),
  (decimal '999.99'),
  (decimal '1000.1'),
  (decimal '101000.1'),
  (decimal '1000000'),
  (decimal '10001100.10')
 ) 
 
SELECT '$' || regexp_replace(replace(cast(d as VARCHAR), '.', ','), '(d)(?=(d{3})+,)', '$1.')
FROM dataset

Output:

_col0
$1,00
$999,99
$1.000,10
$101.000,10
$1.000.000,00
$10.001.100,10