MySQL FORMAT Function

Summary: This tutorial shows you how to use the MySQL FORMAT function to format decimal numbers in various locales.

Introduction to the MySQL FORMAT function

Sometimes, you use an expression or an aggregate function such as AVG to calculate values in the databases such as inventory turnover, the average net price of products, and the average invoice value.

The result of the expression is a decimal with many decimal places. To format those numbers, you use the FORMAT function with the following syntax:

FORMAT(N,D,locale);

The FORMAT function formats the number N to format like ‘#,###,###.##’, rounds to D decimal places. It returns a value as a string.

The FORMAT the function accepts three arguments:

• The N is the number that you want to format.
• The D is the number of decimal places that you want to round.
• The locale is an optional argument that determines the thousand separators and grouping between separators. If you omit the locale operator, MySQL will use en_US it by default. The following link provides all locale names supported by MySQL.

MySQL FORMAT function examples

See the following examples of using the FORMAT function.

SELECT FORMAT(12500.2015, 2);

The following statement uses the FORMAT function with the second argument zero, therefore, the result does not have any decimal places.

SELECT FORMAT(12500.2015, 0);

The following statement uses the de_DE locale instead of the en_US locale:

SELECT FORMAT(12500.2015, 2,'de_DE');

As you see in the result, the de_DE locale use dot (.) for grouping thousand and comma (,) for decimal marks.

Let’s take a look at the products the table in the sample database.

To calculate the stock value of each product, you multiply the quantity in stock and buy price as follows:

SELECT 
    productname, quantityInStock * buyPrice stock_value
FROM
    products;

The result does not look good because there are many decimal places.

To make it better, you can combine two functions:  FORMAT and CONCAT. The FORMAT function formats the stock value rounded to 2 decimal places. And the CONCAT function adds the USD symbol ($) at the beginning of the stock value string:

SELECT 
    productname,
    CONCAT('$',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products;

Notice that the FORMAT the function returns a string value. It means that if you want to sort the results of the FORMAT function using the ORDER BY clause, MySQL will sort the results using string-based not numeric-based.

For example, the following statement sorts the stock values alphabetically.

SELECT 
    productname,
    CONCAT('$',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products
ORDER BY stock_value;

To sort the stock values numerically, you put the expression in the ORDER BY clause as follows:

SELECT 
    productname,
    CONCAT('$',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products
ORDER BY quantityInStock * buyPrice;

In this tutorial, we have shown you how to use the MySQL FORMAT function to format a number in various locale names.