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);
Code language: SQL (Structured Query Language) (sql)
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 the 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);
Code language: SQL (Structured Query Language) (sql)
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);
Code language: SQL (Structured Query Language) (sql)
The following statement uses the de_DE
locale instead of the en_US
locale:
SELECT FORMAT(12500.2015, 2,'de_DE');
Code language: SQL (Structured Query Language) (sql)
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)
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;
Code language: SQL (Structured Query Language) (sql)
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)
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)
In this tutorial, we have shown you how to use the MySQL FORMAT
function to format a number in various locale names.