How to use the MySQL FORMAT() function

The FORMAT() function in MySQL allows you to format a number to a specific format. It is useful for formatting numbers for display purposes.

Syntax

The basic syntax of FORMAT() is:

FORMAT(number,decimals)

Where:

• number is the number to be formatted.
• decimals is the number of decimal places to display.

Examples

Here are some examples of using FORMAT() in MySQL:

1. Format a number to 2 decimal places:

   SELECT FORMAT(123.456, 2);
   
   // Output: 123.46
   

2. Format a number to 0 decimal places (round to integer):

   SELECT FORMAT(123.456, 0);
   
   // Output: 123
   

3. Format a number to 3 decimal places:

   SELECT FORMAT(123.456789, 3);
   
   // Output: 123.457
   

4. Format a negative number to 1 decimal place:

   SELECT FORMAT(-123.456, 1);
   
   // Output: -123.5
   

5. Format a very large number to thousands with comma separator:

   SELECT FORMAT(12345678, 0);
   
   // Output: 12,345,678
   

Other Similar Functions

Other formatting functions in MySQL:

• ROUND(): Rounds a number to a specified number of decimal places
• TRUNCATE(): Truncates a number to a specified number of decimal places
• FORMAT() allows more precise control over formatting and rounding.

So in summary, the FORMAT() function in MySQL provides a flexible way to format numbers for display purposes.