Some DBMSs provide us with the ability to format a number as a currency by providing a format specifier for the currency symbol. Providing such a format specifier allows the DBMS to return the appropriate currency symbol for the locale.

MySQL doesn’t have a currency format specifier, and so we need to do a bit of extra work if we want the currency symbol to be returned.

Example

Here’s an example of returning a number as currency in MySQL:

SELECT CONCAT('$', FORMAT(1234.5678, 2));

Result:

$1,234.57

Here, we used the CONCAT() function to concatenate the currency symbol and the number. We also used the FORMAT() function to format the number in the desired format.

Specify a Locale

The FORMAT() function accepts an optional third argument for the locale. This allows you to format the number using the specified locale.

Example:

SELECT CONCAT('€', FORMAT(1234.5678, 2, 'de_DE'));

Result:

€1.234,57

In this case, our currency symbol is the Euro symbol (€) instead of the dollar sign, and we specified that the number should be formatted using German numbering conventions, which uses a period for the group separator, and a comma for the decimal point.

Formatting in the Application vs Database

It’s usually recommended that currency formatting, etc is done at the application level, rather than at the database level. Application programming environments typically have better formatting capabilities than DBMSs, and it’s usually better to have unformatted data returned by the DB, so that the application can start with a clean slate and format it as needed.

That said, sometimes the situation calls for formatting at the database level, in which case, the above techniques can come in handy.