MySQL - TRUNCATE() Function

MySQL provides a set of functions to perform various numerical functions. The TRUNCATE() function of MySQL is used to limit the given number to the desired number of decimal digits.

This function keeps the specified number of digits after the decimal and removes the remaining.

Syntax

Following is the syntax of this function −

TRUNCATE(x, D);

Parameters

This function accepts two parameters namely −

  • X − A (decimal) number from which you need to limit the decimal digits.

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

Return Value

This function returns the truncated value as a numeric or decimal value.

Example

Following is an example of the TRUNCATE() function. In here, we are limiting the number of digits after the decimal to 3.

SELECT TRUNCATE(225.33654, 3) As Result;

Output

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

Result
225.336

Example

We can pass parameters to this function as string values −

SELECT TRUNCATE('5587.2645165421', '5') As Result;

Output

This will produce the following result −

Result
5587.26451

Example

If the number we choose is 0 the result is always 0 −

SELECT TRUNCATE(0, 1) As Result;

Output

This will produce the following result −

Result
0

Example

If the value of the parameter D (value chosen for the number digits) is 0, all the digits after the decimal will be removed −

SELECT TRUNCATE(6546841.489484, 0) As Result;

Output

On executing the given query, the output is displayed as follows −

Result
6546841

Example

If we pass a negative value for the second parameter (D), all the decimal places are removed from the given number and in addition, the specified number of digits from the given number (left to the decimal point) will be modified as zeros.

SELECT TRUNCATE(2545455.33546, -3) As Result;

Output

On executing the given query, the output is displayed as follows −

Result
2545000

Example

In the following example, let us create a table named CUSTOMERS using the CREATE statement as follows −

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

Now, we are using the MySQL TRUNCATE() function to round the SALARY column to zero decimal places −

SELECT *, TRUNCATE(SALARY, 0) As Result FROM CUSTOMERS;

On executing the given query, the output is displayed as follows −

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