In MySQL, the TRUNCATE() function truncates a value to a specified number of decimal places.

The function accepts two arguments; the value, and the number of decimal places to truncate that value to.

All numbers are rounded toward zero.

Syntax

The syntax goes like this:

TRUNCATE(X,D)

Where X is the value for which you’d like to truncate, and D is the number of decimal places to truncate it to.

Example 1 – Basic Usage

Here’s a basic example to demonstrate.

SELECT TRUNCATE(1.2345, 2);

Result:

+---------------------+
| TRUNCATE(1.2345, 2) |
+---------------------+
|                1.23 |
+---------------------+

Example 2 – Specify No Decimal Point

You can truncate the number to no decimal point by using 0 as the second argument.

SELECT TRUNCATE(1.2345, 0);

Result:

+---------------------+
| TRUNCATE(1.2345, 0) |
+---------------------+
|                   1 |
+---------------------+

Example 3 – Specify a Negative Fractional Part

You can also specify a negative value for the second argument. This causes digits on the left side of the decimal point to become zero. The number of digits affected are determined by the value you provide.

Here’s an example:

SELECT TRUNCATE(123.45, -1);

Result:

+----------------------+
| TRUNCATE(123.45, -1) |
+----------------------+
|                  120 |
+----------------------+

And if we adjust the second argument:

SELECT TRUNCATE(123.45, -2);

Result:

+----------------------+
| TRUNCATE(123.45, -2) |
+----------------------+
|                  100 |
+----------------------+

Example 4 – Expressions

You can also pass in expressions like this:

SELECT TRUNCATE(1 + .2345, 2);

Result:

+------------------------+
| TRUNCATE(1 + .2345, 2) |
+------------------------+
|                   1.23 |
+------------------------+

You can also do this with the second argument:

SELECT TRUNCATE(1.2345, 1 + 1);

Result:

+-------------------------+
| TRUNCATE(1.2345, 1 + 1) |
+-------------------------+
|                    1.23 |
+-------------------------+

Example 5 – Passing in a Function

In this example I pass in the MySQL PI() function as the first argument.

SELECT 
  PI(),
  TRUNCATE(PI(), 2);

Result:

+----------+-------------------+
| PI()     | TRUNCATE(PI(), 2) |
+----------+-------------------+
| 3.141593 |              3.14 |
+----------+-------------------+