MySQL FORMAT() – How to format numbers in MySQL?

By /
FORMAT Function

In this tutorial, we will learn about the MySQL FORMAT() function. In the real world, when you have to write one hundred thousand in figures, you write it as 100,000 and not as 100000. You use some form of formatting by adding the comma to make the number more readable. You must have observed so far that when you output a numerical value in MySQL, it does not return the number with formatting. To format your numerical output in MySQL, we use the FORMAT() function.

MySQL FORMAT() formats a number in the format- ‘#,###.##’, and rounding it to a certain number of decimal places as specified by the user. The result is returned as a string.

Syntax of MySQL FORMAT()

FORMAT(number, decimal_places, locale);Code language: SQL (Structured Query Language) (sql)

Where

  • ‘number’ is the number to be formatted,
  • ‘decimal_places’ is the number of decimal places you want in the formatted number and,
  • ‘locale’ is an optional argument that is used to determine the thousand separators and grouping between separators. If it is not mentioned, MySQL will use ‘en_US’ by default. 

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,

SELECT FORMAT(15023.2265, 2);Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Format Basic Example

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,

SELECT FORMAT(15023.2265, 0);Code language: SQL (Structured Query Language) (sql)

And the output is,

MySQL Format Zero

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.

SELECT FORMAT(230451623.2, 4);Code language: SQL (Structured Query Language) (sql)

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

Format More Decimal Places

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.

SELECT FORMAT(230451623.2, 1, 'de_DE');Code language: SQL (Structured Query Language) (sql)

And the output is,

Format German

The ‘de_DE’ parameter stands for the locale ‘German-Germany’. You can see more locale values here.

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,

SELECT FORMAT(230451623.2, 1, 'en_IN');Code language: SQL (Structured Query Language) (sql)

And the output is,

Format Indian

FORMAT() With Tables

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

Format Employee Table
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,

SELECT eid, Name, CONCAT('$ ',FORMAT(Salary, 2)) AS Salary FROM Employee;Code language: SQL (Structured Query Language) (sql)

And we get the output as,

Format Table Example 1

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.

SELECT Department, CONCAT('$ ', FORMAT(SUM(Salary),2)) AS Salary FROM Employee GROUP BY Department;Code language: SQL (Structured Query Language) (sql)

And the output is,

Format Table Example 2

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.

References

  • MySQL Official Documentation on FORMAT().