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

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

In the SQL, you can also use the TRUNCATE function with the integer field of the table as shown in the following block:

  SELECT TRUNCATE(Integer_Column_Name, Decimal_point ) 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 10.14544 by 3 decimal points:

Output:

Truncate_of_10.14544by4
101.145

Example 2: This example truncates the 908.5478 value by 2 decimal points and returns the truncated value in result:

Output:

Truncate_of_908.5478by2
908.54

Example 3: Here, you get the truncated value of 8.85478 by 3 decimal points:

Output:

Truncate_of_8.85478by3
8.854

Example 4: This example truncates the value of 252.54894 by 2 decimal points:

Output:

Truncate_of_252.54894by200
252.54

Example 5: In this example, we are going to perform the TRUNCATE function on the integer columns of the table.

So, first we will create the new table through which we will perform the TRUNCATE function.

The below CREATE query shows how to create the new table in the Structured Query Language:

  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)  );    

Now, we create the new table Student_Sem_Marks using the above CREATE syntax which helps to store the marks of each semester of college students.

  CREATE TABLE Student_Sem_Marks  (  Student_ID INT,  Student_First_Name Varchar(80),  Student_Last_Name Varchar(80),  First_Sem INT,  Second_Sem INT,  Third_Sem INT,  Fourth_Sem INT,   Total INT,   Percentage INT,  Grade Varchar(10)  );  

The following multiple INSERT queries inserts the records of students with names and their semester marks:

  INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11254, Akash, Sharma, 58.857, 65.547, 98.245, 60.254, 282.9038, 70, B1);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11250, Ram, Sharan, 28.247, 58.987, 78.548, 77.254, 243.036, 60, C2);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11255, Manoj, Gupta, 55.847, 25.548, 68.244, 88.785, 238.424, 78, B1);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11257, Parul, Chaudhary, 85.547, 98.278, 62.525, 35.858, 281.935, 65, B2);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11251, Monu, Sharma, 72.254, 63.354, 36.258, 96.639, 268.505, 67, B2);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11258, Srishti, Chaudhary, 85.589, 78.478, 98.785, 89.698, 352.55, 88, A2);    INSERT INTO Student_Sem_Marks (Student_ID, Student_First_Name, Student_Last_Name, First_Sem, Second_Sem, Third_Sem, Fourth_Sem, Total, Percentage, Grade) VALUES (11260, Bhavesh, Bardiya, 78.256, 87.658, 82.963, 91.365, 340.242, 85, A2);  

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

Student_ID	Student_First_Name	Student_Last_Name	First_Sem	Second_Sem	Third_Sem	Fourth_Sem	Total	Percentage	Grade
11254	Akash	Sharma	58.857	65.547	98.245	60.254	282.903	70	B1
11250	Ram	Sharan	28.247	58.987	78.548	77.254	243.036	60	C2
11255	Manoj	Gupta	55.847	25.548	68.244	88.785	238.424	78	B1
11257	Parul	Chaudhary	85.547	98.278	62.525	35.858	281.935	65	B2
11251	Monu	Sharma	72.254	63.354	36.258	96.639	268.505	67	B2
11258	Srishti	Chaudhary	85.589	78.478	98.785	89.698	352.55	88	A2
11260	Bhavesh	Bardiya	78.256	87.658	82.963	91.365	340.242	85	A2

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

  SELECT Total, TRUNCATE(Total, 2) AS Truncate_of_Total_by2 FROM Student_Sem_Marks;  

This query truncates the total marks to 2 decimal points of each student.

Output:

Total	Truncate_of_Total_by2
282.903	282.90
243.036	243.03
238.424	238.42
281.935	281.93
268.505	268.50
352.55	352.55
340.242	340.24

Query 2: The following SELECT query uses the TRUNCATE function with the First_Sem and Second_Sem column of the above Student_Sem_Marks table:

  SELECT First_Sem, TRUNCATE(First_Sem, 1) AS Truncate_ofFirst_Sem, Second_Sem, TRUNCATE(Second_Sem, 1) AS Truncate_ofSecond_Sem FROM Student_Sem_Marks;  

This query truncates the values of first sem and second sem column to the 1 decimal point.

Output:

First_Sem	Truncate_ofFirst_Sem	Second_Sem	Truncate_ofSecond_Sem
58.857	58.8	65.547	65.5
28.247	28.2	58.987	58.9
55.847	55.8	25.548	25.5
85.547	85.5	98.278	98.27
72.254	72.2	63.354	63.3
85.589	85.5	78.478	78.4
78.256	78.2	87.658	87.6

Next Topic#

TRUNCATE Function in SQL

TRUNCATE Function in SQL

Syntax of TRUNCATE Function

Examples of TRUNCATE function

Assumptions of Cronbach’s Alpha in SPSS

Escaping and Non-Escaping Closures in Swift

You may also like