The MySQL FORMAT function is used for numeric formatting and is different from formatting date and time values. The FORMAT function is used to format a numeric value with a specific number of decimal places and commas as thousands separators.
Syntax
Here’s the basic syntax:
FORMAT(number, [decimal_places])
number: The numeric value that you want to format.
decimal_places (optional): The number of decimal places to round the value to. If omitted, it defaults to 0.
Example
Here’s a simple example:
SELECT FORMAT(1234567.89, 2);
This query will return the formatted number with two decimal places and commas as thousands separators:
1,234,567.89
Formatting a number to two decimal places
SELECT FORMAT(123.4567, 2);
This will return the value 123.46.
Keep in mind that the FORMAT function returns a string, not a numeric value.
The MySQL FORMAT function is a versatile tool for formatting numerical values in a user-friendly manner. It can be used to round numbers to specific decimal places, add thousands separators, and display currency symbols. The FORMAT function is commonly used in data visualization, reporting, and user interfaces to present numerical data in a clear and organized format.