In this article, you will see how the TRUNCATE() function works. The TRUNCATE function in MySQL is used to truncate a number to a specified number of decimal places.
Syntax :
TRUNCATE( X, D)
Parameter :
TRUNCATE() function accepts two parameters as mentioned above and described below.
- X –The number which to be truncated.
- D –Number of decimal places up to which the given number is to be truncated. If it is 0 it removes all the decimal values and returns only the integer. If it is negative, then the number is truncated to the left side of the decimal point.
Returns :
It returns the number after truncated to the specified places.
Example-1 :
Truncating a number when D is 0.
Truncating a Negative number –
SELECT TRUNCATE(-10.11, 0) AS Truncated_Number ;
Output :
+------------------+ | Truncated_Number | +------------------+ | -10 | +------------------+
Truncating a Positive number –
SELECT TRUNCATE(100.61, 0) AS Truncated_Number ;
Output :
+------------------+ | Truncated_Number | +------------------+ | 100 | +------------------+
Example-2 :
Truncating a number when D is negative(-ve).
Truncating a Negative number –
SELECT TRUNCATE(-19087.1560, -3) AS Truncated_Number;
Output :
+------------------+ | Truncated_Number | +------------------+ | -19000 | +------------------+ 1 row in set (0.00 sec)
Truncating a Positive number –
SELECT TRUNCATE(10876.5489, -1) AS Truncated_Number;
Output :
+------------------+ | Truncated_Number | +------------------+ | 10870 | +------------------+
Example-3 :
Truncating a number when D is positive(+ve).
Truncating a Negative number up to 2 decimal places –
SELECT TRUNCATE(-7767.1160, 2) AS Truncated_Number;
Output :
+------------------+ | Truncated_Number | +------------------+ | -7767.11 | +------------------+ 1 row in set (0.00 sec)
Truncating a Positive number up to 3 decimal places –
mysql> SELECT TRUNCATE(17646.6019, 3) AS Truncated_Number;
Output :
+------------------+ | Truncated_Number | +------------------+ | 17646.601 | +------------------+
Example-4 :
TRUNCATE Function can also be used to find the truncated values for the column data. In this example, we are going to find truncated values for Price column. To demonstrate create a table namedProduct
CREATE TABLE Product
(
Product_id INT AUTO_INCREMENT,
Product_name VARCHAR(100) NOT NULL,
Buying_price DECIMAL(13, 6) NOT NULL,
Selling_price DECIMAL(13, 6) NOT NULL,
Selling_Date Date NOT NULL,
PRIMARY KEY(Product_id)
);
Now inserting some data to the Product table :
INSERT INTO
Product(Product_name, Buying_price, Selling_price, Selling_Date)
VALUES
('P6', 1060.865460, 1700.675400, '2020-08-26' ),
('P2', 2000.154300, 3050.986700, '2020-08-27' ),
('P1', 4000.874300, 5070.786500, '2020-08-28' ),
('P2', 2090.654300, 3050.896500, '2020-09-01' ),
('P3', 5900.543280, 7010.654700, '2020-09-04' ),
('P4', 4000.353200, 4500.125400, '2020-09-05' ),
('P5', 5010.768900, 6000.873200, '2020-09-08' ),
('P6', 1060.865460, 1400.675430, '2020-09-11' );
So, the Product Table is :
mysql> SELECT * FROM Product;
Output :
+------------+--------------+--------------+---------------+--------------+ | Product_id | Product_name | Buying_price | Selling_price | Selling_Date | +------------+--------------+--------------+---------------+--------------+ | 1 | P6 | 1060.865460 | 1700.675400 | 2020-08-26 | | 2 | P2 | 2000.154300 | 3050.986700 | 2020-08-27 | | 3 | P1 | 4000.874300 | 5070.786500 | 2020-08-28 | | 4 | P2 | 2090.654300 | 3050.896500 | 2020-09-01 | | 5 | P3 | 5900.543280 | 7010.654700 | 2020-09-04 | | 6 | P4 | 4000.353200 | 4500.125400 | 2020-09-05 | | 7 | P5 | 5010.768900 | 6000.873200 | 2020-09-08 | | 8 | P6 | 1060.865460 | 1400.675430 | 2020-09-11 | | 9 | P6 | 1060.865460 | 1700.675400 | 2020-08-26 | | 10 | P2 | 2000.154300 | 3050.986700 | 2020-08-27 | | 11 | P1 | 4000.874300 | 5070.786500 | 2020-08-28 | | 12 | P2 | 2090.654300 | 3050.896500 | 2020-09-01 | | 13 | P3 | 5900.543280 | 7010.654700 | 2020-09-04 | | 14 | P4 | 4000.353200 | 4500.125400 | 2020-09-05 | | 15 | P5 | 5010.768900 | 6000.873200 | 2020-09-08 | | 16 | P6 | 1060.865460 | 1400.675430 | 2020-09-11 | +------------+--------------+--------------+---------------+--------------+
Now, we are going truncating both Buying_price and Selling_price column up to 2 decimal places.
SELECT Product_name, Buying_price, TRUNCATE(Buying_price, 2) Trucated_Bprice, Selling_price, TRUNCATE(Selling_price, 2) Trucated_Sprice FROM Product ;
Output :
+--------------+--------------+-----------------+---------------+-----------------+ | Product_name | Buying_price | Trucated_Bprice | Selling_price | Trucated_Sprice | +--------------+--------------+-----------------+---------------+-----------------+ | P6 | 1060.865460 | 1060.86 | 1700.675400 | 1700.67 | | P2 | 2000.154300 | 2000.15 | 3050.986700 | 3050.98 | | P1 | 4000.874300 | 4000.87 | 5070.786500 | 5070.78 | | P2 | 2090.654300 | 2090.65 | 3050.896500 | 3050.89 | | P3 | 5900.543280 | 5900.54 | 7010.654700 | 7010.65 | | P4 | 4000.353200 | 4000.35 | 4500.125400 | 4500.12 | | P5 | 5010.768900 | 5010.76 | 6000.873200 | 6000.87 | | P6 | 1060.865460 | 1060.86 | 1400.675430 | 1400.67 | | P6 | 1060.865460 | 1060.86 | 1700.675400 | 1700.67 | | P2 | 2000.154300 | 2000.15 | 3050.986700 | 3050.98 | | P1 | 4000.874300 | 4000.87 | 5070.786500 | 5070.78 | | P2 | 2090.654300 | 2090.65 | 3050.896500 | 3050.89 | | P3 | 5900.543280 | 5900.54 | 7010.654700 | 7010.65 | | P4 | 4000.353200 | 4000.35 | 4500.125400 | 4500.12 | | P5 | 5010.768900 | 5010.76 | 6000.873200 | 6000.87 | | P6 | 1060.865460 | 1060.86 | 1400.675430 | 1400.67 | +--------------+--------------+-----------------+---------------+-----------------+