Some DBMSs have functions that allow us to format numbers as currency just by passing the appropriate format string. This converts the number to a string with the applicable currency symbol, group separator, and decimal point (if relevant).

Other DBMSs don’t make it that easy, and you need to do a bit of work first.

Below are examples of using SQL to format numbers as currency in some of the most popular DBMSs.

Oracle

Oracle Database provides us with the TO_CHAR(number) function, which allows us to format numbers by passing a format model (or format string). We can use this function to return a number as currency.

Oracle provides a few format elements for returning the currency symbol, depending on which currency symbol you require – the local currency symbol, the ISO currency symbol, or the dual currency symbol.

Here’s an example of all three:

ALTER SESSION SET NLS_TERRITORY = 'Denmark';
SELECT 
    TO_CHAR(12345, 'fmL99G999D00') AS "r1",
    TO_CHAR(12345, 'fmC99G999D00') AS "r3",
    TO_CHAR(12345, 'fmU99G999D00') AS "r3"
FROM DUAL;

Result:

            r1              r3            r3 
______________ _______________ _____________ 
kr12.345,00    DKK12.345,00    €12.345,00   

See How to Format a Number as Currency in Oracle for more information and examples.

SQL Server

SQL Server provides the FORMAT() function, which enables us to format numbers and dates. The C format specifier can be used to return a number as a currency:

SELECT FORMAT(1234, 'C');

Result:

$1,234.00

There are other things you can do with the format string, such as specify how many decimal places to return.

The function also accepts an optional third argument, which can be used to specify the locale to use for the number and currency formatting. Different locales use different conventions for their numbers and currency.

See How to Format Numbers as Currency in SQL Server (T-SQL) for more information and examples.

PostgreSQL

PostgreSQL has a money data type that will typically be formatted in the locale currency when output:

SELECT CAST(3145 as money);

Result:

$3,145.00

PostgreSQL also has a TO_CHAR() function that converts the value to a string and formats it based on the format string we supply. Therefore, we can use it to format numbers as currency by passing the appropriate format string.

See How to Format Numbers as Currency in PostgreSQL for more.

MySQL

In MySQL, we have to do a bit more work. MySQL doesn’t provide us with a format specifier for the currency symbol, so we need to provide our own:

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

Result:

$1,234.57

Here, we used the CONCAT() function to concatenate the currency symbol and the number, and we used the FORMAT() function to format the number into our desired format.

It’s possible to specify a locale for the numeric formatting. This determines which characters are used for the group separator and decimal point.

See How to Format Numbers as Currency in MySQL for more information.

MariaDB

MariaDB is very much like MySQL, and we can use the same method that we used with MySQL.

Here’s an example that specifies a locale to use for the number formatting:

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

Result:

€8.790,24

In this case, we supplied the Euro symbol (€) and formatted the number to use the numbering conventions used in Germany.

See Format a Number as Currency in MariaDB for more information.

SQLite

SQLite has a PRINTF() function that allows us to format a number based on a format string. For example, we can use this to format the number to a given number of decimal places, plus add a currency symbol.

SELECT PRINTF("$%.2f", 123.457);

Result:

$123.46

Update: SQLite 3.38.0 (released 22 Feb 2022) renamed the PRINTF() function to FORMAT(). The original PRINTF() name is retained as an alias for backwards compatibility.

So the above example can be changed to this:

SELECT FORMAT("$%.2f", 123.457);

Result:

$123.46

See the SQLite documentation for more on the FORMAT() function.