Javatpoint Logo
Javatpoint Logo

MySQL Number Format Function

FORMAT function in MySQL is used to formats the number as a format of "#, ###. ##", rounded it in certain decimal places. After formatting the number, it will return the value as a string.

This function is beneficial when we calculate values in the databases like inventory turnover, or the average net price of products. The calculated result is a decimal value with many decimal places. In such a case, it is required to format those numbers for user understandable.

Therefore, we use the following syntax of FORMAT function in MySQL

This function accepts three parameters which are discussed below:

Parameter Explanation
N It is a number that we want to format.
D It is a number of decimal places that we want to round the number. If D is 0, the result returns a string without any places.
locale It is an optional argument. It specifies the locale to use that determines the decimal point result, thousands of separators, and grouping between separators. By default, MySQL will use an en_us locale.

MySQL Version Support

The FORMAT function can support the following MySQL versions:

  • MySQL 8.0
  • MySQL 5.7
  • MySQL 5.6
  • MySQL 5.5
  • MySQL 5.1
  • MySQL 5.0
  • MySQL 4.1
  • MySQL 4.0
  • MySQL 3.23.3

MySQL FORMAT Function Example

Let us understand how the FORMAT function works in MySQL through various examples.

The following function formats the number up to three decimal places.

After executing the above function, we will get the below output:

MySQL Number Format Function

Next, the below statement uses the FORMAT function, where the second argument is zero.

After execution, we will get the below output, where we can see that the result has produced without any decimal places.

MySQL Number Format Function

The above two functions use en_US locale by default. It is because we have not provided any locale with the function. Now, this statement uses the de_DE locale instead of en_US.

We will get the output below where we can see that de_DE locale uses (,) comma operator for decimal mark.

MySQL Number Format Function

Let us understand the above function in the database. Suppose our database has the following table that contains the data as below:

MySQL Number Format Function

We can calculate the stock value of the shirt using the below statement:

It will give output as below that do not looks good because of many decimal places.

MySQL Number Format Function

We can make it better using the FORMAT function. Execute the below statement that returns the stock value up to two decimal places.

It will give the output as below.

MySQL Number Format Function

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Trending Technologies

B.Tech / MCA