MySQL FORMAT() Function

Introduction

In this chapter, we will learn about the FORMAT() function in MySQL. The FORMAT() function is used to format a number as a string with a specified number of decimal places and optional thousands separators. This function is useful for presenting numeric data in a more readable and standardized format. We will cover the syntax for the FORMAT() function, a complete example of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the FORMAT() function in MySQL is:

SELECT FORMAT(number, decimal_places[, locale]);

• number: The number to be formatted.
• decimal_places: The number of decimal places to display.
• locale: Optional. The locale to use for the number formatting (e.g., ‘en_US’).

Complete Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the FORMAT() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    salary DECIMAL(10, 2) NOT NULL
);

INSERT INTO employees (first_name, last_name, salary) VALUES
('Rahul', 'Sharma', 50000.75),
('Priya', 'Singh', 60000.50),
('Amit', 'Kumar', 70000.00);

2. Using FORMAT() to Format Numbers

We will use the FORMAT() function to format the salary column with two decimal places.

SELECT
    first_name,
    last_name,
    FORMAT(salary, 2) AS formatted_salary
FROM employees;

Output:

3. Formatting Numbers with Locale

We will use the FORMAT() function to format the salary column with two decimal places and a specific locale.

SELECT
    first_name,
    last_name,
    FORMAT(salary, 2, 'en_IN') AS formatted_salary_in
FROM employees;

Output:

Note: The actual formatting may vary based on the locale settings of your MySQL server.

Important Considerations

• Locale Support: The FORMAT() function supports various locales, which can affect the formatting of numbers (e.g., decimal and thousands separators). Ensure that the locale you specify is supported by your MySQL server.
• Performance: Formatting numbers using the FORMAT() function can impact performance, especially on large datasets. Use this function primarily for presentation purposes and consider formatting numbers in your application code if performance is a concern.
• Decimal Places: The decimal_places parameter determines the number of decimal places in the formatted output. Ensure that this value meets your presentation requirements.

Conclusion

The FORMAT() function in MySQL is used for formatting numbers as strings with specified decimal places and optional thousands separators. This chapter covered the syntax for using the FORMAT() function, provided a complete example of its usage, and discussed important considerations.