FORMAT_CURRENCY()

Documentation

VoltDB Home » Documentation » Using VoltDB

FORMAT_CURRENCY()

FORMAT_CURRENCY() — Converts a DECIMAL to a text string as a monetary value.

Synopsis

FORMAT_CURRENCY( decimal-value, rounding-position )

Description

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

Example

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;