MySQL FORMAT Function

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

Let us see how to write this MySQL String function to format numbers with an example, and the basic syntax of it is as shown below.

FORMAT(Number, Number of Decimals, Locale)

MySQL Format Function Example

The following query shows multiple ways to use this 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 method uses en_US as the locale.

SELECT FORMAT(98765.43219, 0);

SELECT FORMAT(98765.43219, 1);

SELECT FORMAT(98765.43219, 2);
Changing or controlling Decimal Values of a Number 1

In this example, we are using more number of decimal values. So, to fill the gap, this MySQL method 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 convert 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

String Format Example 2

It also allows you to 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 `mathemetical functions`;
MySQL FORMAT Function 4