MySQL TRUNCATE()

Definition of MySQL TRUNCATE()

MySQL TRUNCATE() function is specified to return a literal number after trimming it to a certain number of decimal places mentioned in the query. The number to be truncated and a number of decimal places are signified as the arguments or parameters of this TRUNCATE() function. For this function, we use two components: one is a numeric expression that is to be truncated up to the number defined by the second parameter given in the TRUNCATE() function and the other is a number that indicates up to what decimal positions the first number is to be trimmed. For the execution of the TRUNCATE() function based SQL statement we need to add both parameters.

Syntax:

The following syntax is specified in MySQL to find out the resultant number from the TRUNCATE() function.

TRUNCATE(Any_Number,Decimal_Value)

The parameters used in the above function syntax are described in brief below:

• Any_Number: Denotes the number to be used for truncate function.
• Decimal_Value: Indicatesnumber of decimal places required to truncate the Any_Number parameter.

In case, when the second argument of the function, Decimal_Value is supposed to be negative then the function causes the Decimal_Value digits left of the Any_Number’s decimal point to make it zero. Therefore, once the Decimal_Valueis zero then, the resultant value has no fractional part in it.

How TRUNCATE() Function Works in MySQL?

Supposing that we have an SQL statement for a description of the TRUNCATE() function as follows:

SELECT TRUNCATE(543.145,0);

The value returned is a numeric expression that is truncated to 0 decimal spaces. MySQL supports this MySQL TRUNCATE() function responsible to trim a number to a particular number of decimal positions.

While for Oracle and PostgreSQL, we have to use TRUNC() function which works to provide identical functionality as the TRUNCATE() function. However, for SQL Server the ROUND() function is used along with the third argument in the function that defines the truncation process. The syntax is denoted as ROUND(Num, Dec, f). If the f parameter is not zero, then the ROUND() function performs the Dec number of decimal points and rounds Num number to return the required value when executed.

Examples to Implement TRUNCATE() in MySQL

Let us consider the following examples by using the function TRUNCATE() in MySQL:

1. MySQL TRUNCATE() Function with Positive Decimal Places Number

Suppose we take the below query statement to perform the TRUNCATE() function execution:

SELECT TRUNCATE(2.444, 1);

Here, we see that since the second argument i.e. a number of decimal places is 1, so the TRUNCATE() function generates the value with only one decimal point.

2. MySQL TRUNCATE() function with Negative Decimal Places Number

The below SQL statement shows how to add the TRUNCATE() function has a negative number of decimal positions.

The SQL query is:

SELECT TRUNCATE(233.33, -2);

Here, from the above statement, we can clear that the TRUNCATE() function causes the negative number digits left of the decimal point of the literal number, 233.33, and makes to become zero. So, as (-2) partis 0 then the resultant value has no fractional section.

3. MySQL TRUNCATE() Function and ROUND() Function Comparison

We have selected the two SQL statements to apply the TRUNCATE() & ROUND()MySQL functions and display the difference between both the functional resultant.

Let us consider the examples as follows:

SELECT TRUNCATE(288.88,2);

SELECT ROUND(288.88,2);

We can now consider that the ROUND() function indicates the return value that is rounded off and the TRUNCATE() function signifies the value returned that is trimmed up to the decimal places as mentioned in the above SQL query.

4. MySQL TRUNCATE() Function used in Table Columns

The TRUNCATE() function shows the trimmed value of the column values when added with the SQL statement along with SELECT, GROUP BY, ORDER BY clauses, and INNER JOIN like SQL keywords.

Let us consider the below statements to create two tables as sample tables to proceed further:

Person Table Query:

CREATE TABLE Person(Person_ID INT NOT NULL PRIMARY KEY, Person_Name varchar(255)NOT NULL, Person_Address varchar(255)NOT NULL);

Employee Table Query:

CREATE TABLE Employee (Person_ID INT NOT NULL PRIMARY KEY, Employee_Name varchar(255)NOT NULL, Salary INT NOT NULL);

After this let us insert some values in both the tables with the SQL query below:

INSERT INTO Person (Person_ID, Person_Name, Person_Address) VALUES
('101', 'Aakash', 'Delhi'),
('102', 'Kajol', 'Mumbai'),
('103', 'Chetan', 'Pune'),
('104', 'Ankit', 'Goa');
INSERT INTO Employee (Person_ID, Employee_Name, Salary) VALUES
('101', 'Aakash', '1045.234'),
('102', 'Kajol', '1901.123'),
('103', 'Chetan', '2110.456'),
('104', 'Ankit', '2490.246');
select * from Employee;

select * from Person;

After these entries of few records, let us move towards the queries related to the execution of TRUNCATE() function.

Here, is the SQL query:

SELECT Employee_Name, TRUNCATE(AVG (Salary),0)Avg_Salary FROM Employee a
INNER JOIN Person b ON b.Person_ID = a.Person_ID GROUP BY Person_Name ORDER BY Avg_Salary;

In the above illustration, we have used the TRUNCATE() function to eliminate all the numbers placed after the decimal digits from the average salary column in the table.

So, the TRUNCATE () may be helpful to produce the result set when we need to truncate the number values in a specific column of the database table to a certain numeric value of decimals. It provides us the integer numbers in return to fetch the row as desired.

5. MySQL TRUNCATE with Some Other Examples

We have some more examples to explain the TRUNCATE() function and its importance in MySQL to trim the specific decimal places of a number.

SELECT TRUNCATE(251.315, -2);

The above MySQL statement returns the value by truncating 251.315 up to 1 decimal place.

SELECT TRUNCATE(-251.315, 0);

SELECT TRUNCATE(478.231, 0);

Thus, the TRUNCATE() function returns the number which is truncated to given decimal places. If the decimal places value or parameter of the TRUNCATE() function is skipped then the number to be truncated is trimmed to 0 fractional points. Remember that the second argument of the TRUNCATE() function should be either a positive or negative integer.

Conclusion

If we notice the TRUNCATE() function works similar to the MySQL ROUND() function on the basis of decreasing the number of decimal positions. But this function is not responsible to perform any sort of rounding result as that of ROUND() function. To make clear, we can conclude that the MySQL TRUNACATE() helps to trim the decimal places but ROUND() function executes the rounding process.

Recommended Articles

This is a guide to MySQL TRUNCATE(). Here we also discuss the definition and how truncate() function works in mysql? along with different examples and its code implementation. You may also have a look at the following articles to learn more –

1. MySQL IN Operator
2. Unique Key in MySQL
3. MySQL Subquery
4. ROLLUP in MySQL