MySQL FORMAT Function

The MySQL Format function is one of the MySQL String Functions, which is useful to round the decimal values or format numbers as per the country, locale, and returns that number as a string value.

Let us see how to write a String Format in MySQL to format numbers with an example. And the basic syntax of the Format Number in MySQL is as shown below.

FORMAT(Number, Number of Decimals, Locale)

MySQL Format Function Example 1

The following query shows multiple ways to use this MySQL Number format function. Here, we used only two arguments.

First, We used 0 means, and it removes all the decimals. Next, 1 means it keeps one decimal value. Remember, by default, and the String Function uses en_US as the locale.

SELECT FORMAT(98765.43219, 0);

SELECT FORMAT(98765.43219, 1);

SELECT FORMAT(98765.43219, 2);
MySQL FORMAT Function 1

In this MySQL FORMAT example, we are using more number of decimal values. So, to fill the gap, this MySQL function adds extra zeros to the values.

SELECT FORMAT(98765.43219, 4);

SELECT FORMAT(98765.43219, 6);

SELECT FORMAT(98765.43219, 8);
MySQL FORMAT Function 2

Here, we used the locale argument to change or format the numbers.

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

SELECT FORMAT(98765.43219, 4, 'ru_RU');

SELECT FORMAT(98765.43219, 3, 'en_IN');
MySQL FORMAT Function 3

MySQL String Format Example 2

The MySQL Number format also allows you to format or round the decimal values of a column data. In this example, We are going to use this MySQL string Format on Standard Cost and Sales Amount columns

SELECT EnglishProductName, 
      Color,
      StandardCost,
      FORMAT(StandardCost, 2) AS Cost, 
      SalesAmount,
      FORMAT(SalesAmount, 5) AS Sale,
      TaxAmt
FROM `MySQL Tutorial`.`mathemetical functions`;
MySQL FORMAT Function 4