how to format number separated by comma for every three integer digits in presto

user_5042610 0
Viewed 4672

I want to format a number separated by comma for every three integer digits. for example 12345.894 -- > 12,345.894. I have no clue how to format it. I have tried for an example but no luck.

format('%,.2f', 12345.894)

The above code will round decimal to 2 digits so it returns 12,345.89. In my case, I want to keep the decimal 12,345.894.

trino presto number-formatting
2 Answers

You could use regular expression:

SELECT regexp_replace(cast(123456.8943 as VARCHAR), '(\d)(?=(\d{3})+\.)', '$1,')
  Results:
  -------
  123,456.8943

Some explanation:

First we cast to varchar as regex works on string.

The regex actually says: replace any digit \d you see only if it has one or more + groups of 3 digits \d{3} just before the "." (dot) sign \.. The digit is replaced by the same digit $1 but with comma after it ,.

The example can be seen here.

You can see more discussions on the regex here.

user_52872210

If you want 3 decimal numbers you can use %,.3f as the format string:

presto> select format('%,.3f', 12345.894);
   _col0
------------
 12,345.894
(1 row)
user_29587520
Related