Search code examples
number-formattingprestotrino

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

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.

Solution

  • 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.