MySQL FORMAT() Function

 The MySQL FORMAT() function is used to format a number as a string with a specified number of decimal places, and it includes thousand separators (commas, by default) for better readability. It is particularly useful for displaying numerical data in a more human-readable format.

Syntax:

FORMAT(number, decimal_places[, locale])

Parameters:

1. number: The numeric value to format.
2. decimal_places: The number of decimal places to include in the result.
3. locale (optional): Specifies the locale for formatting. If not provided, the default locale is used (usually en_US).

Returns:

• A formatted string representation of the number.

---

Examples:

1. Basic Formatting:

   SELECT FORMAT(1234567.891, 2);
   

   Result: 1,234,567.89

2. Formatting with Zero Decimal Places:

   SELECT FORMAT(1234567.891, 0);
   

   Result: 1,234,568

3. Using a Different Locale:

   SELECT FORMAT(1234567.891, 2, 'de_DE');
   

   Result: 1.234.567,89

   • In the de_DE locale, periods (.) are used as thousand separators, and commas (,) as decimal separators.

4. Negative Numbers:

   SELECT FORMAT(-1234567.891, 3);
   

   Result: -1,234,567.891

5. No Thousand Separator: If you don't want the thousand separator, you'll need to manipulate the string or use a different function, as FORMAT() always includes it.

---

Notes:

• If decimal_places is negative or not a number, MySQL will throw an error.
• The function returns a string, not a number, so it may need to be converted back to numeric format for calculations.