Most of the major RDBMSs have functions that enable us to format numbers with commas as either the group separator, or the decimal separator.

Some RDBMSs also output certain numeric data types with commas in the relevant place.

Below are examples of formatting numbers with commas in some of the more popular DBMSs.

MySQL

In MySQL, we can use the FORMAT() function to format numbers with commas:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

There’s no need to specify where the commas should go. The function knows where to put them.

This function also accepts a third argument to specify the locale. Not all locales use a comma as the group separator – some locales use a comma as the decimal separator. The FORMAT() function is smart enough to know which convention to use.

Example:

SELECT FORMAT(123456.789, 2, 'de_DE');

Result:

123.456,79

Oracle

In Oracle we can use the TO_CHAR() function to format a number with commas:

SELECT TO_CHAR(12345, 'fm99G999D00')
FROM DUAL;

Result:

12,345.00

The G format element is a placeholder for the group separator, and the D is for the decimal separator.

In this case, the group separator outputs a comma, and the decimal character outputs a period. That’s because my current session’s NLS_TERRITORY parameter is set to Australia.

It’s true that we could just use a hardcoded comma instead of the G format element, but that assumes that the group separator is a comma. Not all locales use a comma as the group separator.

See How to Format a Number with a Comma in Oracle for more information and examples for formatting numbers with commas in Oracle.

SQL Server

In SQL Server we can use the FORMAT() function to format numbers with commas. This function accepts the number and a format string:

SELECT 
    FORMAT(123456.789, 'N') AS "Number",
    FORMAT(123456.789, 'P') AS "Percent",
    FORMAT(123456.789, 'C') AS "Currency";

Result:

+------------+----------------+-------------+
| Number     | Percent        | Currency    |
|------------+----------------+-------------|
| 123,456.79 | 12,345,678.90% | $123,456.79 |
+------------+----------------+-------------+

The function also accepts an optional “culture” argument for specifying the locale to use for the formatted output.

You can also construct your own custom format string so that you can determine precisely where each thousands separator and decimal separator goes.

See How to Format Numbers with Commas in SQL Server for examples.

PostgreSQL

PostgreSQL has a TO_CHAR() function that works like Oracle’s function of the same name:

SELECT TO_CHAR(123456.78, 'fm999G999D99');

Result:

123,456.78

This function is locale aware, so it will present the appropriate group and decimal separators for the locale.

PostgreSQL also has a money data type, which is output in a locale aware format. See Format Numbers with Commas in PostgreSQL for an example.

SQLite

SQLite is a bit more limited when compared to most of the other DBMSs. However, it does have a printf() function that allows us to format numbers according to a format string:

SELECT printf("%,d", 123456789);

Result:

123,456,789

This works fine with integers, but real/floating point numbers require a bit more work. See Format Numbers with a Comma in SQLite for more about this.

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("%,d", 123456789);

Result:

123,456,789

MariaDB

In MariaDB, we can use the FORMAT() function to format a number with commas:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

As with the MySQL function of the same name, there’s no need to specify where the commas should go. The function knows where to put them.