MySQL Number Format Function

If you are interested to learn about the MySQL Signal Resignal

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:43.1M698Hello Java Program for Beginners

What is the function of number format?

The number_format() function is an inbuilt function in PHP which is used to format a number with grouped thousands. It returns the formatted number on success otherwise it gives E_WARNING on failure.

Can you format numbers in SQL?

Starting from SQL Server 2012, you can format numeric types using the T-SQL FORMAT() function. This function accepts three arguments; the number, the format, and an optional “culture” argument. It returns a formatted string of type nvarchar. The format is supplied as a format string

How do I format in MySQL?

MySQL FORMAT() returns the number N to a format like ‘#,###,###.##’ rounded to a number of decimal places and returns the result as a string. If there is no decimal point, decimal place is defined as 0. A number which is to be formatted up to D decimal places rounded up.

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.

Examples of MySQL FORMAT()

Let us take a look at a basic example. I have the following number – 15023.2265. Difficult to read at first glance right? Let us format it using the FORMAT() function. The resulting number should have only two places of decimals. The query for it is,

And the output is,

As you can see, we get a formatted number. You can also see that FORMAT() rounded off .2265 to .23 to display the number up to two decimal places.

MySQL FORMAT() With Zero Decimal Places

Suppose you do not want to see decimal places in your formatted number. What can be done? Well, the ‘decimal_places’ argument should be set to 0. The query for it is,

And the output is,

MySQL FORMAT() to Add More Decimal Places Than The Original Number

Consider the following number – 230451623.2. Suppose you want the number to be formatted and the resulting number should display 4 decimal places. Wait, but we only have one decimal place in the number so, how does FORMAT() handle this? Let us look at the below example.

FORMAT() will add three zeros after .2 to make it 4 decimal places. We can see that in the output below:

MySQL FORMAT() With The Locale Argument

So far, we were displaying formatted numbers with no ‘locale’ parameter specified. In all the above examples, MySQL assumed ‘locale’ to have the default value ‘en_US’. So for an American, 230,451,623.2 would be easily readable but that wouldn’t be the case for a German or an Indian where a different separator notation is followed between the thousands. Let us display 230451623.2 with such a locale that will follow the German separator notation.

And the output is,

The ‘de_DE’ parameter stands for the locale ‘German-Germany’. You can see more locale values .Let us now display the same number with the locale followed in India. We will use the value ‘en-IN’ meaning ‘English-India’. The query is,

And the output is,

FORMAT() With Tables

Let us see some examples of FORMAT() with Tables. Consider the below ‘Employee’ table.

Let us format the values of the Salary column using FORMAT() so that it is more readable. Using FORMAT(), we will also show the Salary value up to two decimal places. While we’re at it, let’s add some more formatting. How about prepending the dollar sign before every salary value? We will be using the SELECT statement, aliases, and the CONCAT function for this. The query is,

And we get the output as,

Using the GROUP BY clause and the SUM() function, let us find out the sum of salaries by the department by using the similar formatting we used in the previous query.

And the output is,

Conclusion

FORMAT() is one of the key functions to format your numerical data to make it more presentable. For further reading, do check out the references.