Most major RDBMSs provide ways for us to format numbers using SQL. Some provide only basic formatting capabilities, while others are more advanced.

Below are examples of formatting a number in some of the more popular RDBMSs.

MySQL

In MySQL, we can use the FORMAT() function to format a number to a specific format. This function accepts three parameters; the number, the number of decimal places, and an optional locale:

SELECT FORMAT(123456.789, 2);

Result:

123,456.79

As mentioned, you can also pass a third argument to specify the locale to use for the formatting pattern. See How to Format Numbers in MySQL for an example.

To format numbers as currency, see How to Format Numbers as Currency in MySQL.

Oracle

Oracle Database provides us with a number of options when it comes to formatting numbers.

In particular, the TO_CHAR(number) function allows us to format a number based on our own custom format model.

Example:

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

Result:

$12,345.00

The first argument is the number to format, and the second argument is the format model. The format model consists of one or more format elements, each of which represent a different element in the formatted output.

In the above example, we use use the 9 character to represent each digit, and the 0 character to output the digit or zero (depending on whether or not a digit exists in that position). We also use G for a locale aware group separator and D for a locale aware decimal separator. The fm element suppresses any leading or trailing padding that might be applied to the result.

Here’s a full list of format elements that you can use for your format model when formatting numbers.

There are other ways to format numbers in Oracle. For example, the LPAD()function can be used to convert numbers to a string and format them exactly as you like on the fly. Functions like CAST() can also have an effect on how a number is formatted, depending on the data type that it’s being cast as.

See How to Format Numbers in Oracle for more detail, and examples.

PostgreSQL

Like Oracle, PostgreSQL also has a TO_CHAR() function, and it works in pretty much the same way:

SELECT TO_CHAR(923, 'fm000G000th');

Result:

000,923rd

Here, I used the 0 format element to output leading zeros, even when the original number didn’t have any digits in that place. In this case, I used the th format element to convert the number to its ordinal. I also used G for a locale aware group separator.

Here’s a list of template patterns the format string can consist of when formatting numbers, as well as the format modifiers.

Another way to format a number in PostgreSQL is to convert it to the money data type. Numbers stored as the money type are outputted in a format that’s locale aware, with the appropriate currency symbol, group separator, decimal separator, etc:

SELECT CAST(10000 as money);

Result:

$10,000.00

See How to Format Numbers in PostgreSQL for more examples of formatting numbers in PostgreSQL.

SQL Server

SQL Server has the FORMAT() function, which works much like Oracle and PostgreSQL’s TO_CHAR() function, in that it enables you to format numbers based on 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 |
+------------+----------------+-------------+

This example demonstrates three standard format specifiers – each of which output the number in a specific format.

We can also use our own custom format specifiers, which allow us to craft our own format, in case the standard format isn’t what we want.

You can also do things like, format numbers in their hexadecimal equivalent, or using exponential notation, etc.

And you can pass an optional third argument to specify the locale for which to format the number (it formats the number using the conventions of that locale).

See How to Format Numbers in SQL Server for more examples.

MariaDB

Like MySQL, MariaDB has a FORMAT() function, which provides basic number formatting.

Here’s an example of using this function to format a number using different locales:

SELECT 
    FORMAT(123456.789, 2, 'ta_IN') AS "Tamil, India",
    FORMAT(123456.789, 2, 'de_DE') AS "German, Germany",
    FORMAT(123456.789, 2, 'zh_HK') AS "Chinese, Hong Kong";

Result:

+--------------+-----------------+--------------------+
| Tamil, India | German, Germany | Chinese, Hong Kong |
+--------------+-----------------+--------------------+
| 1,23,456.79  | 123.456,79      | 123,456.79         |
+--------------+-----------------+--------------------+

See How to Format Numbers in MariaDB for more examples.

SQLite

SQLite is more limited than the other DBMSs when it comes to formatting numbers. However, there are ways to get some basic number formatting:

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

Result:

123,456,789

The PRINTF() function can output group separators for integers, but not for real/floating point numbers. To format real/floating point numbers, you’d need to do a bit more work. See Format Numbers with a Comma in SQLite for an example of what I mean.

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