2

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.

Shawn
  • 47,241
  • 3
  • 26
  • 60
Jason LiLy
  • 634
  • 2
  • 9
  • 19

2 Answers2

4

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.

Chananel P
  • 1,704
  • 1
  • 18
  • 19
2

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)
Martin Traverso
  • 4,731
  • 15
  • 24
  • no, I want to keep the decimal as is. for example 12345.87634 --> 12,345.87634, 12345.23476534 --> 12,345.2346534 – Jason LiLy Jul 07 '20 at 20:53
  • There's no way to do that, unfortunately. The `%f` format specifier has a fixed number of decimals and defaults to 6. See https://docs.oracle.com/en/java/javase/11/docs/api/java.base/java/util/Formatter.html#dndec – Martin Traverso Jul 09 '20 at 00:50
  • In my opinion, you can build the part of "3" dynamically and concat as the first parameter. e.g., select format('%,.' || cast(calculated_length as varchar) || 'f' ,value) – ebyhr Jul 09 '20 at 13:07