CODEWITHSUNDEEP
postgresqlpostgresql-14
MAK
MAK

Reputation: 7260

Number format equivalent function in PostgreSQL

Trying to format the number as per the given format and culture.

Given:

-4059587.225000, --Value
'#,##0.00;(#,##0.00)' --Format
'en-US' --Culture

Will have many patterns, the given one is for example.

Expected output: (4,059,587.23)

In SQL Server we have format() function, what's the equivalent in PostgreSQL?

My try:

select to_char( -4059587.225000, '#,##0.00;(#,##0.00)' );

Error:

multiple decimal points

Upvotes: 0

Views: 291

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246788

Use to_char:

SET lc_numeric = 'en_US';

SELECT translate(to_char(-4059587.225000, '9G999G999D99PRFM'), '<>', '()');

   translate    
════════════════
 (4,059,587.23)
(1 row)

The documentation describes the available formats.

Upvotes: 2

Reto
Reto

Reputation: 1343

How about:

select 
concat( to_char( -4059587.225000, '#,##0.00;' ), to_char( -4059587.225000, '(#,##0.00)' ) );

Upvotes: 0

Related Questions