CODEWITHSUNDEEP
sqlpostgresql
Super Mario
Super Mario

Reputation: 939

Example for converting numeric result of function to string with commas

I try to make TOTAL_MONEY to this format "123,456,789" (with commas).

select entity_id,
       'the sum is:' || to_char(TOTAL_MONEY, ',') as text_msg
from(
select entity_id,
       sum(volume) as TOTAL_MONEY
from procurement) t

but it doesn't work. I don't find here something useful.

Can you help me with that?

Upvotes: 2

Views: 118

Answers (2)

Abelisto
Abelisto

Reputation: 15614

Yet another, simpler solution:

select
  entity_id,
  to_char(TOTAL_MONEY, '"the sum is: "FM999,999,999,999') as text_msg
from(
  select entity_id, sum(volume) as TOTAL_MONEY
  from procurement) t

There are: 'FM' option to remove unnecessary spaces; "double-quoted string" to print it as-is without replacements of any patterns/placeholders.

Upvotes: 1

Anuraag Veerapaneni
Anuraag Veerapaneni

Reputation: 679

    select entity_id,
    'the sum is:' || ltrim(rtrim(to_char(TOTAL_MONEY, '999,999,999,999'))) as text_msg
    from(
          select entity_id,
          sum(volume) as TOTAL_MONEY
          from procurement
        ) t

Upvotes: 1

Related Questions