Number Format Function - (MySQL Misc)

The Number Format function in MySQL is used to format a given number as a string. It allows you to specify the number of decimal places, the decimal separator, and the thousands separator.

Syntax

The syntax for the Number Format function in MySQL is as follows:

FORMAT(number, decimals, decimal_separator, thousands_separator)

• number: Required. The number to be formatted.
• decimals: Optional. The number of decimal places to display. Defaults to 0.
• decimal_separator: Optional. The character to use as the decimal separator. Defaults to "." (period).
• thousands_separator: Optional. The character to use as the thousands separator. Defaults to "," (comma).

Example

Let's say we want to format the number 123456.789 to have two decimal places, a comma as the thousands separator, and a period as the decimal separator. Here's how we can do it:

SELECT FORMAT(123456.789, 2, '.', ',');

The output of this query will be:

"123,456.79"

Explanation

In the example above, we used the Number Format function to format the number 123456.789. We specified that we wanted it to be displayed with two decimal places, a comma as the thousands separator, and a period as the decimal separator.

By default, the function uses a period as the decimal separator and a comma as the thousands separator. However, we specified the opposite by passing in ".", "," as the decimal and thousands separators, respectively.

Use

The Number Format function is useful when you want to format a number for display purposes. It can be used to make large numbers more readable by adding thousands separators, or to round numbers to a certain number of decimal places.

Important Points

• The Number Format function only works on numbers. If you pass in a non-numeric value, it will return NULL.
• The decimal and thousands separators can be any single character.
• If you don't specify the number of decimal places, the function will default to 0.
• If you don't specify the decimal or thousands separators, the function will default to "." and "," respectively.

Summary

In this tutorial, we covered the Number Format function in MySQL. We went over its syntax, provided an example, explained its output, and discussed its use and important points. With this knowledge, you can now use the Number Format function to format numbers in MySQL.