How to Use FORMAT Function in MySQL: Syntax and Examples

In MySQL, use the FORMAT(number, decimal_places) function to format a number with commas as thousands separators and specify decimal places. For example, FORMAT(1234567.89, 2) returns '1,234,567.89' as a formatted string.

Syntax

The FORMAT function formats a number with commas and decimal places. It takes two arguments:

• number: The numeric value to format.
• decimal_places: The number of digits after the decimal point.

The result is a string with commas separating thousands and the specified decimal precision.

sql

FORMAT(number, decimal_places)

Example

This example shows how to format a number with two decimal places and commas as thousands separators.

sql

SELECT FORMAT(1234567.89123, 2) AS formatted_number;

Output

+------------------+
| formatted_number  |
+------------------+
| 1,234,567.89     |
+------------------+

Common Pitfalls

Common mistakes when using FORMAT include:

• Expecting a numeric result instead of a string. FORMAT returns a string, so you cannot use it directly for further numeric calculations.
• Not specifying the decimal places, which can lead to unexpected formatting.
• Using FORMAT on non-numeric values causes errors.

sql

/* Wrong: Using FORMAT result in numeric calculation */
SELECT FORMAT(1234.56, 2) + 1000 AS wrong_sum;

/* Right: Convert back to number if needed */
SELECT CAST(REPLACE(FORMAT(1234.56, 2), ',', '') AS DECIMAL(10,2)) + 1000 AS correct_sum;

Output

+-----------+
| wrong_sum |
+-----------+
| 1000      |
+-----------+

+-------------+
| correct_sum |
+-------------+
| 2234.56     |
+-------------+

Quick Reference

Function	Description	Example	Output
FORMAT(number, decimals)	Formats number with commas and decimals	FORMAT(12345.678, 2)	'12,345.68'
CAST(expression AS DECIMAL)	Converts string to decimal number	CAST('1234.56' AS DECIMAL(10,2))	1234.56
REPLACE(string, from_str, to_str)	Replaces substring in string	REPLACE('1,234.56', ',', '')	'1234.56'

Key Takeaways

Use FORMAT(number, decimal_places) to format numbers with commas and decimals as a string.

FORMAT returns a string, so convert back to number if you need to do math.

Always specify decimal places to control the output precision.

Avoid using FORMAT on non-numeric values to prevent errors.