MySQL Truncate() Function

In this context, we will learn how we can use the MYSQL TRUNCATE function to truncate a number to a mentioned number of decimal places.

Syntax of MySQL Truncate Function

A number to a mentioned number of decimal places is truncated by the MYSQL TRUNCATE function, which is shown as follows

TRUNCATE ( Y, Z );

Description of above Syntax:

In the above-written syntax,

• Y is a numeric expression or a literal number which is to be truncated.
• Z is the number of decimal places to truncate to. The TRUNCATE function acts as Z digits left of the decimal point when the Z is negative. On the other hand, when the Z is zero, then it will return the value which has no decimal point.

But the important point is that Both Y and Z are needed.
There is a point to be noticed that in terms of reducing the decimal number, the TRUNCATE function is similar to the ROUND function. Although it is similar to that as the ROUND function rounds, the TRUNCATE function does not do that.

Application used for truncate() function:

The TRUNCATE() function can be used in the given below MySQL versions.

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Example of MySQL truncate function:

Now we will see some examples with the help of the TRUNCATE function.

1) Example of MySQL truncate function with a positive number of decimal places:

Let's visualize the example given below:

SELECT TRUNCATE ( 1.555, 1 );

Now the output is:

1 row in set (0.00 sec)

Due to the number of decimal places arguments being 1, so the MySQL TRUNCATE function keeps only 1 decimal place in the return value.

2) Example Of MySQL Truncate Function With A Negative Number Of Decimal Places:

The example given below shows us the example of the TRUNCATE function with a negative number of decimal places:

SELECT TRUNCTE ( 199.99 , -2 );

Now the output is:

1 row in set (0.00 sec)

3) MySQL Truncate() Vs. Round():

The below example takes the help of both TRUNCATE and ROUND functions for comparison:

SELECT
TRUNCATE (1.999, 1);
ROUND(1.999, 1) ;

Now the query output is:

1 row in set (0.00 sec)

As we can see in the above output, the TRUNCATE function only trims the decimal places while the ROUND() function executes the rounding.

In the above context, we have learned how we can use the TRUNCATE function in MySQL to truncate a number to a mentioned number of the decimal place.