MySQL - FORMAT() Function

The MySQL FORMAT() function is used to format a numeric value with a specified number of decimal places and commas as a thousands separator. It also rounds the given number with the specified number of digits after the decimal.

This function does nothing but modifying a number for simpler mathematical calculations. It can only be invoked on numerical data of a database.

Syntax

Following is the syntax of MySQL Format() function −

FORMAT(X,D);

Parameters

This function accepts two values as parameters −

  • X − A number (floating point).

  • D − Number of digits after the decimal place that should be kept.

Return Value

This function returns the formatted number as a string with commas for thousands and the specified decimal places.

Example

In the following query, we are using the MySQL FORMAT() function to format the given number with three decimal places −

SELECT FORMAT(85878.687555758, 3) As Result;

Output

The output for the query above is produced as given below −

Result
85,878.688

Example

The following query formats the given number with two decimal places and returns the result −

SELECT FORMAT(424812, 2) As Result;

Output

This will produce the following result −

Result
424,812.00

Example

We can also pass values to this function in the form of strings −

SELECT FORMAT('454877.3387557', '3') As Result;

Output

The output is displayed as follows −

Result
454,877.339

Example

If we pass 0 as the second argument of this function, it omits all the digits after the decimal and returns the result −

SELECT FORMAT(54889578795.65785478, 0) As Result;

Output

This will produce the following result −

Result
54,889,578,796

Example

You can also use the FORMAT() function to round the values of a column in a table. To do so, let us first create a table named CUSTOMERS using the below query −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

The below query adds 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To verify whether the records are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 Kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

The following query formats the SALARY column without decimal places −

SELECT ID, NAME, ADDRESS, FORMAT(SALARY, 0) FROM CUSTOMERS;

The output for the query above is produced as given below −

ID NAME ADDRESS SALARY
1 Ramesh Ahmedabad 2,000
2 Khilan Delhi 1,500
3 Kaushik Kota 2,000
4 Chaitali Mumbai 6,500
5 Hardik Bhopal 8,500
6 Komal Hyderabad 4,500
7 Muffy Indore 10,000
Advertisements