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 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 a thousand separators and grouping between separators. If you omit the locale operator, MySQL will use en_US 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);

Try It Out

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);

Try It Out

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

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

Try It Out

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

Let’s take a look at the products 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;Code language: SQL (Structured Query Language) (sql)

Try It Out

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

To make it better, you can combine two functions:  FORMATand CONCAT. The FORMAT function formats the stock value rounded to 2 decimal places. 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;Code language: SQL (Structured Query Language) (sql)

Try It Out

Notice that the FORMAT 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;Code language: SQL (Structured Query Language) (sql)

Try It Out

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;Code language: SQL (Structured Query Language) (sql)

Try It Out

Summary

• Use the MySQL FORMAT() function to format a number in various locale names.