MySQL FORMAT(): Format Numeric Output

FORMAT() rounds a number to the requested number of decimal places and returns a formatted string with grouping separators.

FORMAT(X, D [, locale])

X is the number, D is the number of decimal places, and the optional locale controls grouping and decimal separators.

SELECT FORMAT(12332.123456, 4) AS formatted_value;

12,332.1235

Formatting a calculated value

If score stores the total score and nov stores the number of votes, you can format the calculated average for display:

SELECT FORMAT(score / NULLIF(nov, 0), 2) AS avg_value
FROM table_name
WHERE id = 2;

Do not store display formatting unnecessarily

The original tutorial used FORMAT() inside an UPDATE. If avg_score is a numeric column, use numeric rounding instead of storing a comma-formatted string:

UPDATE table_name
SET avg_score = ROUND(score / NULLIF(nov, 0), 3)
WHERE id = 2;

If you only need formatted output, keep the stored value numeric and apply FORMAT() in the final SELECT.

Locale formatting

SELECT FORMAT(12332.2, 2, 'de_DE') AS formatted_value;

Related math tutorials

All Math Functions · ROUND() · TRUNCATE() · AVG() · DATE_FORMAT()

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: