MySQL FORMAT() String Function

MySQL FORMAT() String Function

The MySQL FORMAT() string function formats a number to a specified number of decimal places and returns the result as a string. This function is essential for displaying numbers in a human-readable format in SQL queries.

Syntax

SELECT FORMAT(number, decimal_places[, locale]) AS result
FROM table_name;

The FORMAT() function has the following components:

  • number: The number to be formatted.
  • decimal_places: The number of decimal places to format the number to.
  • locale (optional): The locale to use for the formatting. The default is 'en_US'.
  • result: An alias for the resulting formatted string.
  • table_name: The name of the table from which to retrieve the data.

Example MySQL FORMAT() String Function

Let's look at some examples of the MySQL FORMAT() string function:

Step 1: Using the Database

USE mydatabase;

This query sets the context to the database named mydatabase.

MySQL USE DATABASE

Step 2: Creating a Table

Create a table to work with:

CREATE TABLE sales (
    id INT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL
);

This query creates a table named sales with columns for id and amount.

MySQL CREATE TABLE

Step 3: Inserting Initial Rows

Insert some initial rows into the table:

INSERT INTO sales (amount)
VALUES (1234.56),
       (7890.12),
       (3456.78),
       (9101.11),
       (2345.67);

This query inserts five rows into the sales table.

MySQL INSERT INTO TABLE

Step 4: Using FORMAT() with WHERE Clause

Use the FORMAT() function to format a number to two decimal places:

SELECT amount, FORMAT(amount, 2) AS formatted_amount
FROM sales;

This query retrieves the amount column from the sales table and returns the amount formatted to two decimal places.

MySQL FORMAT() WITH WHERE CLAUSE

Step 5: Using FORMAT() with Multiple Columns

Use the FORMAT() function with multiple columns:

SELECT id, amount, FORMAT(amount, 2) AS formatted_amount
FROM sales;

This query retrieves the id and amount columns from the sales table and returns the amount formatted to two decimal places.

MySQL FORMAT() WITH MULTIPLE COLUMNS

Step 6: Using FORMAT() with Locale

Use the FORMAT() function with a specified locale:

SELECT amount, FORMAT(amount, 2, 'de_DE') AS formatted_amount_german
FROM sales;

This query retrieves the amount column from the sales table and returns the amount formatted to two decimal places using the German locale.

MySQL FORMAT() WITH LOCALE

Conclusion

The MySQL FORMAT() function is a powerful tool for displaying numbers in a human-readable format in SQL queries. Understanding how to use the FORMAT() function is essential for effective data querying and analysis in MySQL.