FORMAT_CURRENCY() — Converts a DECIMAL to a text string as a monetary value.
FORMAT_CURRENCY( decimal-value, rounding-position )
The FORMAT_CURRENCY() function converts a DECIMAL value to its string representation, rounding to the specified position. The resulting string is formatted with commas separating every three digits of the whole portion of the number (indicating thousands, millions, and so on) and a decimal point before the fractional portion, as needed.
The rounding-position argument must be an integer between 12 and -25 and indicates the place to which the numeric value should be rounded. Positive values indicate a decimal place; for example 2 means round to 2 decimal places. Negative values indicate rounding to a whole number position; for example, -2 indicates the number should be rounded to the nearest hundred. A zero indicates that the value should be rounded to the nearest whole number.
Rounding is performed using "banker's rounding", in that any fractional half is rounded to the nearest even number. So, for example, if the rounding-position is 2, the value 22.225 is rounded to 22.22, but the value 33.335 is rounded to 33.34. The following list demonstrates some sample results.
|FORMAT_CURRENCY( .123456789, 4) = 0.1235|
|FORMAT_CURRENCY( 123456789.123, 2 ) = 123,456,789.12|
|FORMAT_CURRENCY( 123456789.123, 0 ) = 123,456,789|
|FORMAT_CURRENCY( 123456789.123, -2 ) = 123,456,800|
|FORMAT_CURRENCY( 123456789.123, -6 ) = 123,000,000|
|FORMAT_CURRENCY( 123456789.123, 6 ) = 123,456,789.123000|
The following example uses the FORMAT_CURRENCY() function to return a DECIMAL column as a string representation of its monetary value, rounding to two decimal places and appending the appropriate currency symbol from a VARCHAR column.
SELECT country, currency_symbol || format_currency(budget,2) AS annual_budget FROM world_economy ORDER BY country;