MySQL FORMAT Function | Format a Number after Rounding

The FORMAT Function is an inbuilt function in MySQL. It converts a number in a format like “#,###,###.###” after rounding it to a specified number of decimal places. Also, it returns the number as a string. In this article, we will discuss the MySQL FORMAT Function. Also, we will discuss a few examples of using it.

Syntax

FORMAT(number, decimal_places)

Parameters

The MySQL FORMAT Function expects two parameters. The description of the parameters is as follows:

• number: The first parameter specifies the number to format.
• decimal_places: The second parameter specifies the decimal places to return. Also, if this parameter is 0, the function returns the number with no decimal places.

Return Value

The function returns a string after converting the input number to a format like “#,###,###.###” after rounding to a specified number of decimal places.

Examples

Let’s consider a few examples of using the function.

Example 1: Two decimal places

For instance, consider an example to format the number upto two decimal places.

mysql> SELECT FORMAT(2352341.3557, 2) as formattedNumber;
+-----------------+
| formattedNumber |
+-----------------+
| 2,352,341.36    |
+-----------------+

In the above example, the function formats the input number upto two decimal places and returns a string.

Example 2: Rounding to 0 Decimal Places

mysql> SELECT FORMAT(2352341.9557, 0) as formattedNumber;
+-----------------+
| formattedNumber |
+-----------------+
| 2,352,342       |
+-----------------+

Similarly, in the above example, the function rounds the input number and returns the string with no decimal places.

Conclusion

In conclusion, we discussed the MySQL FORMAT Function. You can read more about it on the Official MySQL Documentation. Additionally, you can learn about more MySQL String Functions on Concatly.