MySQL FORMAT Function

The MySQL Format is one of the String Functions, which is useful to round the decimal values or the numbers as per the country, and 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 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, 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 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);
format decimal values Example 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');
format with locale argument Example 3

String Format Example 2

It also allows you to round the decimal values of column data. In this example, We are going to use this string Format on the 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