FORMAT
Rounds a number and formats it as a string with thousands separators.
MySQL 8.4
FORMAT(num, decimals[, locale])- [ ]optional part
- num
- Number to formatDECIMAL
- decimals
- Number of decimal places, a negative one counts as 0INT
- locale
- Locale rules for writing numbers, for example de_DE or ru_RU, en_US by defaultVARCHAROptional
Return value
A string like 1,234,567.89; NULL if num is NULL
Examples
Thousands separators
MySQL 8.4
SELECT FORMAT(1234567.891, 2) AS formatted;German and Russian notation
MySQL 8.4
SELECT FORMAT(1234567.891, 2, 'de_DE') AS de,
FORMAT(1234567.891, 2, 'ru_RU') AS ru;Spending of each family member
MySQL 8.4
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.