The FORMAT() function in MySQL is used to format numbers with commas as thousands separators and round them to a specified number of decimal places. It is helpful for displaying numbers in a more human-readable format, especially in financial or statistical data.
Syntax
FORMAT(number, decimal_places)
Parameters
| Parameter | Description |
|---|---|
number |
Required. The numeric value to format. |
decimal_places |
Required. The number of decimal places to include in the formatted output. If set to 0, no decimal places are included. |
Definition and Use
- Purpose: Converts a number into a formatted string with thousands separators and rounded to the specified number of decimal places.
- Output: The result is returned as a string, not a numeric type.
Usage Examples
Example 1: Formatting with Two Decimal Places
Format 250500.5634 to two decimal places:
SELECT FORMAT(250500.5634, 2) AS FormattedNumber;
Output:
| FormattedNumber |
|---|
| 250,500.56 |
Explanation: The number is rounded to two decimal places, and commas are added as thousands separators.
Example 2: Formatting with Zero Decimal Places
Format 250500.5634 to zero decimal places:
SELECT FORMAT(250500.5634, 0) AS FormattedNumber;
Output:
| FormattedNumber |
|---|
| 250,501 |
Explanation: The number is rounded to the nearest whole number, and commas are added.
Example 3: Formatting Negative Numbers
Format a negative number with two decimal places:
SELECT FORMAT(-12345.678, 2) AS FormattedNumber;
Output:
| FormattedNumber |
|---|
| -12,345.68 |
Explanation: The negative sign is preserved, the number is rounded to two decimal places, and commas are added.
Example 4: Formatting with No Decimal Places
If decimal_places is explicitly set to 0:
SELECT FORMAT(1234567.89, 0) AS FormattedNumber;
Output:
| FormattedNumber |
|---|
| 1,234,568 |
Technical Details
- Availability: Available from MySQL 4.0 onwards.
- Return Type: The result is always returned as a string.
- Rounding: The function rounds numbers to the nearest value based on the specified
decimal_places.
Applications
- Display Formatting: Enhance the readability of numeric data in reports or dashboards.
- Currency Values: Format numbers as monetary amounts with two decimal places.
- Data Export: Prepare formatted output for exporting or presenting data.
Key Notes
- The function does not modify the original data; it only formats it for display or output.
- Since the result is a string, it cannot be directly used for further numeric calculations without conversion.
- The format output uses the default locale for thousands and decimal separators. Custom locale-based formatting is not supported natively in
FORMAT().
The FORMAT() function is a simple and efficient way to present numbers in a clean, human-readable format, making it an essential tool for handling and displaying numeric data in MySQL.