MySQL 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
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.
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.
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.
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:
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.
It will give the output as below.
