Truncate function in SQL

The TRUNCATE is a numeric function in SQL which truncates the number according to the particular decimal points.

Syntax of TRUNCATE Function

SELECT TRUNCATE(X, D) AS Alias_Name;

In the TRUNCATE syntax, X is the integer number and D is the decimal points.

In the Structured Query Language, we can also use the TRUNCATE function with the columns of the table as shown in the following block:

SELECT TRUNCATE(Column_Name1, Column_Name2) AS Alias_Name FROM Table_Name;

In this syntax, we have to define the name and columns of that table on which we want to perform the TRUNCATE function. 

Examples of TRUNCATE function

Example 1: This example gets the truncate value by truncated 101 by 4:

SELECT TRUNCATE(10.14544, 4) AS Truncate_of_101by4;

Output:

Truncate_of_10.14544by4

101.1454

Example 2: This example divides 0.0145 by 4 and returns the truncate value in result:

SELECT TRUNCATE(0.0145, 2) AS Truncate_of_0.0145by2;

Output:

Truncate_of_0.0145 by2

0.01

Example 3: This example divides 8 by 5 and returns the truncate value in result:

SELECT TRUNCATE(0, 5) AS Truncate_of_0by5;

Output:

Truncate_of_0by5

0

Example 4: This example divides 255 by 200 and returns the truncate value in result:

SELECT TRUNCATE(255, 200) AS Truncate_of_255by200;

Output:

Truncate_of_255by200

55

Example 5: This example uses the TRUNCATE function with the SQL table.

In this example, we will create the new table through which we will perform the TRUNCATE function on the columns of the table:

The following shows the syntax to create the new table in SQL:

CREATE TABLE Name_of_New_Table
(
First_Column_of_table Data Type (character_size of First Column),  
Second_Column_of_table Data Type (character_size of the Second column ),  
Third_Column_of_table Data Type (character_size of the Third column),  
…….,  
Last_Column_of_table Data Type (character_size of the Last column)
);  

The following CREATE statement creates the Product_Details table for storing the price and quantity of products:

CREATE TABLE Product_Details
(
Product_ID INT NOT NULL,
Product_Name Varchar(50),
Product_Quantity INT,
Purchasing_Price INT,
Selling_Price INT,
Release_Date Date, 
Product_Rating INT
);

The following multiple INSERT queries insert the records of products with their selling and purchasing price into the Product_Details table:

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (104, P1, 10, 945, NULL, 2022-04-30, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (202, P4, 15, 45, 75, 2022-01-28, 5);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (103, P2, 18, 25, NULL, 2022-02-18, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (111, P7, 25, 5, 15, 2021-12-25, 9);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (210, P6, 15, 50, 70, 2021-10-15, NULL);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (212, P8, 19, 110, 250, 2022-01-28, 4);

INSERT INTO Product_Details (Product_ID, Product_Name, Product_ Quantity Purchasing_Price, Selling_Price, Release_Date, Product_Rating) VALUES (112, P10, 10, 550, 835, 2022-04-11, NULL);

The following SELECT statement displays the inserted records of the above Product_Details table:

SELECT * FROM Product_Details; 

Product_ID	Product_Name	Product_Quantity	Purchasing_Price	Selling_Price	Release_Date	Product_Rating
104	P1	10	945	NULL	2022-04-30	NULL
202	P4	15	45	75	2022-01-28	5.9858
103	P2	18	25	NULL	2022-02-18	42.85789
111	P7	25	5	15	2021-12-25	9
210	P6	15	50	70	2021-10-15	NULL
212	P8	19	110	250	2022-01-28	4
112	P10	10	550	835	2022-04-11	NULL

Query 1: The following SELECT query uses the TRUNCATE function with the Product_Quantity column of the above Product_Details table:

SELECT Product_ID, TRUNCATE(Product_ID, 1) AS Truncate_of_ProductID_by100 FROM Product_Details;

This query truncated each product_id by 1 decimal point and returns the value after truncated.

Output:

Product_ID	Truncate_of_ProductID_by1
104.582	104.5
202.685	202.6
103.935	103.9
111.985	111.9
210.2548	210.2
212.258	212.2
112.3657	112.3

Query 2: The following SELECT query uses the TRUNCATE function with the and Purchasing_Price and Selling_Price column of the above Product_Details table:

SELECT Purchasing_Price, Product_Quantity, TRUNCATE(Purchasing_Price, 2) AS Truncate_ofpurhcaseprice, Selling_Price, Product_Quantity, TRUNCATE(Selling_Price, 2) AS Truncate_of_SellingPrice FROM Product_Details;

This query truncates the purchasing price and selling price of each product by 2 decimal points and returns the truncated value.

Output:

Purchasing_Price		Truncate_ofpurhcaseprice	Selling_Price		Truncate_ofsellingprice
945.	2	945.00	NULL	2
45	2	45.00	75	2	75.00
25	2	25.00	NULL	2
5	2	5.00	15	2	15.00
50	2	50.0	70	2	70.00
110	2	110.00	250	2	250.00
550	2	550.00	835	2	835.00

Query 3: The following SELECT query uses the TRUNCATE function with the Product_Rating column of the above Product_Details table:

SELECT TRUNCATE(Product_Rating, 2) AS Truncate_ofratingby2 FROM Product_Details;

This query truncates each rating of product by 2 decimal points and returns the truncated value.

Output:

Product_Rating	Truncate_ofratingby2
NULL	-
5.9858	5.98
42.85789	42.85
9	9.00
NULL	-
4	4.00
NULL	-