SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO SELECT

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL

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_IDProduct_NameProduct_QuantityPurchasing_PriceSelling_PriceRelease_DateProduct_Rating
104P110945NULL2022-04-30NULL
202P41545752022-01-285.9858
103P21825NULL2022-02-1842.85789
111P7255152021-12-259
210P61550702021-10-15NULL
212P8191102502022-01-284
112P10105508352022-04-11NULL

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_IDTruncate_of_ProductID_by1
104.582104.5
202.685202.6
103.935103.9
111.985111.9
210.2548210.2
212.258212.2
112.3657112.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_ofpurhcasepriceSelling_Price Truncate_ofsellingprice
945.2945.00NULL2 
45245.0075275.00
25225.00NULL2 
525.0015215.00
50250.070270.00
1102110.002502250.00
5502550.008352835.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_RatingTruncate_ofratingby2
NULL-
5.98585.98
42.8578942.85
99.00
NULL-
44.00
NULL-



ADVERTISEMENT
ADVERTISEMENT