MySQL MariaDB

How to Use the FORMAT() Function in MySQL?

While working with the most popular database known as MySQL, you may need to format the numerical values in the large dataset of the database. In such cases, the FORMAT() function plays a crucial role by making it easy to format the numerical data within database tables. It allows you to format a number in a specific way, such as adding decimal places, using a thousand separators, etc.

This guide will illustrate in-depth information on how to use the FORMAT() function in MySQL.

How to Use the MySQL FORMAT() Function?

The “FORMAT()” function in MySQL is utilized to format the number in a proper format, like a specific number of decimal places, commas as thousand separators, etc.

To learn how to use the “FORMAT()” function in MySQL, refer to the syntax provided below:

FORMAT([number_value], [decimal_places])

In the above syntax, “[number_value]” represents a numeric value that needs to be formatted, while the “[decimal_places]” defines how many decimal numbers will be there after the formatting.

Let’s move on to the examples to explore the use cases of the FORMAT() function.

Example 1: Formatting a Number With Two Decimal Places and Thousand Separators

To format a number with a thousand separators and two decimal places, specify a particular number in place of “[number_value]” and “2” as a “[decimal_places]” within the argument of the “FORMAT()” function. A precise example with a number value “96588.1234” is given below:

SELECT FORMAT(96588.1234, 2);

The “SELECT” statement is being used with the “FORMAT()” function in the above example.

Output

The output displayed the number in a proper format (thousand separators and 2 decimals).

Example 2: Formatting a Number With Two Decimal Places and a Particular Symbol

To format a number with a currency symbol and two decimal places, use the “$” symbol and FORMAT() function as the argument of the CONCAT() function:

SELECT CONCAT('$', FORMAT(96588.1234, 2));

In the above example, “96588.1234” is the number value that will be formatted.

Output

The output depicted the number with two decimal places, and a currency symbol.

Example 3: Formatting a Negative Number With Parentheses

To format a negative number with parentheses, the CONCAT() function can be utilized as follows:

SELECT CONCAT('(', FORMAT(-96588.1234, 2), ')');

In the above example, the “CONCAT()” function is taking a total of three arguments: 1st and 3rd arguments are the parentheses and the 2nd argument is the “FORMAT()” function, used to format the negative value.

Output

The output showed that the negative number has been formatted into parenthesis.

Example 4: Format the Table Data

To format the table’s data, you can use the “FORMAT()” function in place of the columns name as shown in the below example:

SELECT id, name,
CONCAT('$', FORMAT(price, 1)) AS 'Formatted Price'
FROM linuxhint_products;

In the above example, the “price” column of the “linuxhint_products” table is formatted by using the “FORMAT()” function.

Output

The output depicts the table’s data with the “price” column in a proper format.

Conclusion

The FORMAT() function in MySQL is a powerful tool for formatting numbers in a specific way, such as adding decimal places, using a thousand separators, etc. The FORMAT() function can also be used as an argument of any other function for making a number in a more appealing format, such as formatting a number with any symbol (“$”, “(”, “)” ). This blog has demonstrated the use of the FORMAT() function in MySQL.

About the author

Danish Ghafoor

I am a computer science graduate with a certification in cybersecurity. My passion for technology has led me to develop a deep understanding of the challenges and solutions in the field of information security. I am dedicated to staying up-to-date with the latest industry trends and technologies, and constantly seeking out new opportunities to expand my knowledge and skills.