FORMAT
Rounds a number and formats it as a string with thousands separators.
MySQL 8.1
FORMAT(num, decimals[, locale])Parameters
- numDECIMAL
- Number to format
- decimalsINT
- Number of decimal places, a negative one counts as 0
- localeVARCHARoptional
- Locale rules for writing numbers, for example de_DE or ru_RU, en_US by default
Return value
A string like 1,234,567.89; NULL if num is NULL
Examples
Thousands separators
MySQL 8.1
SELECT FORMAT(1234567.891, 2) AS formatted;German and Russian notation
MySQL 8.1
SELECT FORMAT(1234567.891, 2, 'de_DE') AS de,
FORMAT(1234567.891, 2, 'ru_RU') AS ru;Spending of each family member
MySQL 8.1
SELECT family_member,
FORMAT(SUM(amount * unit_price), 0) AS spent
FROM Payments
GROUP BY family_member
ORDER BY SUM(amount * unit_price) DESC;Details
The result is a string, so you cannot sort or compare by it as by a number: '9,000' comes out greater than '10,000'. Sort by the source expression and use FORMAT only for output. Halves are rounded away from zero, as in ROUND.