How to format numbers in PostgreSQL?

Question

( SELECT SUM(misyukko.dataint11) 
   FROM misyukko 
   where syouhinid='$kokyakuorderbango'
) AS total_amount

For Example:

If the sum is 12345 it will be displayed as 123,45

If the sum is 12345678 it will be displayed as 123,456,78

How to solve it?

Tim Biegeleisen · Accepted Answer · 2020-09-17 09:05:44Z

3

You could use REGEXP_REPLACE here:

WITH yourTable AS (
    SELECT 12345 AS num
)

SELECT RTRIM(REGEXP_REPLACE(num::text, '(\d{3})', '\1,', 'g'), ',') AS num_out
FROM yourTable;

Demo

answered Sep 17, 2020 at 9:05

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.