How to Format Numbers in MySQL

1. Convert the Format of Numbers Using the FORMAT() Function in MySQL
2. Formatting Numbers Using the round() Function in MySQL

In this tutorial, we will learn how to format numbers in MySQL.

It is frequently required to obtain the appropriate formatting scheme associated with a particular number. For example, the number 1110.00 is written as 1110,00 in Germany since , is used in Germany to separate decimal point values.

Therefore, we need to have some method to convert numbers to a particular format in our database. Let us understand how to get this done.

Convert the Format of Numbers Using the FORMAT() Function in MySQL

The FORMAT() function rounds a number to a specified number of decimal places before formatting it to a format like "#,###,###.##" and returning the result as a string data type.

The FORMAT() syntax is illustrated as follows.

SELECT FORMAT("Enter your number to be formatted", formatting_logic_goes_here) AS your_answer

With the help of the above query, our number to be formatted will be rewritten based on the formatting logic passed.

Now, let us try to consider the German problem mentioned earlier. We can use the following query to represent numbers in German format in MySQL.

SELECT REPLACE(REPLACE(REPLACE(FORMAT("1111.00", 2), ".", "@"), ",", "."), "@", ",") AS your_answer

Note: The above code used the alias your_answer with the AS keyword in MySQL.

The output of the query mentioned above is illustrated as follows:

your_answer
1.111,00

As we can see, we have successfully manipulated our number from 1110.00 to 1110,00. Now, let us try to take another example.

Let us assume that we would like to change the number 123.4567 to 123.45. This would mean we want to eliminate decimal point values after the second decimal value.

This can be done with the help of the round function.

Formatting Numbers Using the round() Function in MySQL

The round() function takes in two values. The value to be rounded and the decimal points to which the number should be rounded.

The basic syntax of the round function is illustrated as follows.

SELECT round(number_to_be_rounded, rounding_logic as your_answer

Now let us try to work on our problem mentioned before. The following query can be used to get it done.

SELECT round(123.4567,2) AS your_answer;

The output of the above query is illustrated as follows.

your_answer
123.46

As we can see, our number was rounded to the nearest value up to two decimal places as required.

Note: The above code used the alias your_answer with the AS keyword in MySQL.

Therefore, with the help of the FORMAT and round functions, we can efficiently format numbers using in-built modules in MySQL.