MySQL FORMAT Function

 MySQL FORMAT Function

The FORMAT() function in MySQL is used to format numbers in a specific way, including decimal places and the grouping of thousands. This function can be especially useful for displaying numbers in a readable format or converting them to a specific locale.

Syntax

FORMAT(number, decimal_places, locale)

• number: The number you want to format.
• decimal_places: The number of decimal places to round the result to. This is optional and defaults to 0 if not provided.
• locale: The locale to format the number according to. This is optional and defaults to the system's current locale (if not specified).

Usage

1. Formatting with Decimal Places The FORMAT() function is commonly used to round numbers to a specific number of decimal places.

   SELECT FORMAT(1234567.8910, 2);
   

   Output:

   FORMAT(1234567.8910, 2)
   1,234,567.89

   • The number is formatted with two decimal places and comma-separated thousands.

2. Formatting without Decimal Places You can format numbers without any decimal places by specifying 0 as the number of decimal places.

   SELECT FORMAT(1234567.8910, 0);
   

   Output:

   FORMAT(1234567.8910, 0)
   1,234,568

   • The number is rounded to the nearest integer and comma-separated.

3. Formatting with Locale You can specify the locale to format the number according to specific regional conventions (e.g., using periods instead of commas for thousands or decimals).

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

   Output:

   FORMAT(1234567.8910, 2, 'de_DE')
   1.234.567,89

   • In this case, the result is formatted according to the German locale, where a period is used for grouping and a comma is used for decimals.

Example Use Cases

1. Displaying Currency Values You can use the FORMAT() function to format monetary values with two decimal places and commas for thousands.

   SELECT FORMAT(1234567.8910, 2);
   

   Output:

   FORMAT(1234567.8910, 2)
   1,234,567.89

   This is useful when displaying currency values in an application.

2. Formatting Large Numbers When working with large numbers, the FORMAT() function helps make the values easier to read by adding commas as thousands of separators.

   SELECT FORMAT(9876543210, 0);
   

   Output:

   FORMAT(9876543210, 0)
   9,876,543,210

3. Locale-Specific Formatting You can display numbers in a locale-specific way, which can be helpful for internationalization in your application.

   SELECT FORMAT(1234567.8910, 2, 'fr_FR');
   

   Output:

   FORMAT(1234567.8910, 2, 'fr_FR')
   1 234 567,89

   • The result uses the French locale where the space is used for grouping and the comma is used for decimal places.

Conclusion

The FORMAT() function in MySQL is an easy way to format numeric values for better presentation, especially when dealing with currency, large numbers, or locale-specific formatting. You can control the number of decimal places and use regional settings to display numbers in a format that is suitable for your audience.