Number Format Function

FORMAT function in MySQL is used to formats the number as a format of “#, ###. ##”, rounded it in certain decimal places. After formatting the number, it will return the value as a string.

This function is beneficial when we calculate values in the databases like inventory turnover, or the average net price of products. The calculated result is a decimal value with many decimal places. In such a case, it is required to format those numbers for user understandable.

Therefore, we use the following syntax of FORMAT function in MySQL

FORMAT(N, D, locale);  

This function accepts three parameters which are discussed below:

Parameter	Explanation
N	It is a number that we want to format.
D	It is a number of decimal places that we want to round the number. If D is 0, the result returns a string without any places.
locale	It is an optional argument. It specifies the locale to use that determines the decimal point result, thousands of separators, and grouping between separators. By default, MySQL will use an en_us locale.

MySQL Version Support

The FORMAT function can support the following MySQL versions:

• MySQL 8.0
• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23.3

MySQL FORMAT Function Example

Let us understand how the FORMAT function works in MySQL through various examples.

The following function formats the number up to three decimal places.

mysql> SELECT FORMAT(12342.123456, 3);  

After executing the above function, we will get the below output:

Next, the below statement uses the FORMAT function, where the second argument is zero.

mysql> SELECT FORMAT(13600.2021, 0);  

After execution, we will get the below output, where we can see that the result has produced without any decimal places.

The above two functions use en_US locale by default. It is because we have not provided any locale with the function. Now, this statement uses the de_DE locale instead of en_US.

mysql> SELECT FORMAT(13600.2021, 2, 'de_DE');  

We will get the output below where we can see that de_DE locale uses (,) comma operator for decimal mark.

Let us understand the above function in the database. Suppose our database has the following table that contains the data as below:

We can calculate the stock value of the shirt using the below statement:

mysql> SELECT name, stock * MRP AS stock_value FROM shirts;  

It will give output as below that do not looks good because of many decimal places.

We can make it better using the FORMAT function. Execute the below statement that returns the stock value up to two decimal places.

mysql> SELECT i,   

FORMAT(stock*MRP, 2) AS stock_value   

FROM shirts ORDER BY stock_value;d, name

It will give the output as below.