MySQL Format Function

By /
MySQL FORMAT Function

The Format function in MySQL is a very useful and advanced formatting tool that allows you to format the output of certain SQL values according to your requirements. In this tutorial, we will learn how to use the Format function in order to format dates and numbers into the desired style.

Introduction

The Format Format function is used to format the number N and round it to D decimal places. The value returned is in the form of a string. It is important to keep in mind that the Format function can only be used with double-precision values. The syntax for the following is-

FORMAT(N,D,locale);Code language: SQL (Structured Query Language) (sql)

Here, the function accepts three arguments-

  • N – The number we want to format.
  • D – The number of decimal places till which we want to round the number.
  • locale – This determines many separators and grouping between separators. By default it is set to en_US locale.

Example of MySQL FORMAT function

Now we will see some examples of the FORMAT function-

Select Format(18657.29476, 3);Code language: SQL (Structured Query Language) (sql)

Output-

Example
Example

Now let us set the locale to de_DE –

SELECT FORMAT(18657.29476, 3,'de_DE');Code language: SQL (Structured Query Language) (sql)

Output-

Example 2
Example 2

Here, de_DE has used (.) for grouping thousands and (,) for decimal places.

Now let us take a look at the database classicmodels. We will use the productstable of the database to look at an example of FORMAT function. The code is:

Select productname, quantityInStock * buyPrice total
From products;Code language: SQL (Structured Query Language) (sql)

Output-

Example 3
Example 3

Here we can see that the decimal place is rounded to 2 places. Let us add a $ sign to it and round it off to 1 decimal place. The code for the following is:

SELECT 
    productname,
    CONCAT('$',
            FORMAT(quantityInStock * buyPrice, 2)) stock_value
FROM
    products;Code language: SQL (Structured Query Language) (sql)

Output-

Example 4
Example 4

Conclusion

In this tutorial we learned how to use the MySQL FORMAT function and change the locale of the function to get different depiction of the number. It was a simple example just to show how it works, but in our upcoming tutorial, we’ll be using this function for more complicated things like creating charts and graphs.

Thanks for reading!