In MariaDB, TRUNCATE() is a built-in numeric function that returns a given number, truncated to a given number of decimal places.

Syntax

The syntax goes like this:

TRUNCATE(X,D)

Where X is the value to truncate, and D specifies how many decimal places to truncate it to.

Example

Here’s an example:

SELECT TRUNCATE(1.25817, 2);

Result:

+----------------------+
| TRUNCATE(1.25817, 2) |
+----------------------+
|                 1.25 |
+----------------------+

Here are some more:

SELECT 
    TRUNCATE(1.25817, 1),
    TRUNCATE(1.25817, 2),
    TRUNCATE(1.25817, 3),
    TRUNCATE(1.25817, 4);

Result (using vertical output):

TRUNCATE(1.25817, 1): 1.2
TRUNCATE(1.25817, 2): 1.25
TRUNCATE(1.25817, 3): 1.258
TRUNCATE(1.25817, 4): 1.2581

Negative Decimal Places

The second argument can be a negative value if required. Passing a negative value causes digits to the left of the decimal place to become zero.

Example:

SELECT TRUNCATE(5824.17, -2);

Result:

+-----------------------+
| TRUNCATE(5824.17, -2) |
+-----------------------+
|                  5800 |
+-----------------------+

Compared to ROUND()

The TRUNCATE() function is different to the ROUND() function. The ROUND() function rounds the number up in some instances and down in others. The TRUNCATE() function, on the other hand, simply truncates the number without rounding.

Here’s a comparison to demonstrate this difference:

SELECT 
    TRUNCATE(3.6789, 2),
    ROUND(3.6789, 2);

Result:

+---------------------+------------------+
| TRUNCATE(3.6789, 2) | ROUND(3.6789, 2) |
+---------------------+------------------+
|                3.67 |             3.68 |
+---------------------+------------------+

It’s also different to the FLOOR() function, which returns the largest integer value not greater than its argument. FLOOR() doesn’t accept a second argument like ROUND() and TRUNCATE() do (it only ever returns an integer anyway).

Non-Numeric Arguments

Here’s what happens when we provide a non-numeric argument:

SELECT TRUNCATE('Ten', 'Two');

Result:

+------------------------+
| TRUNCATE('Ten', 'Two') |
+------------------------+
|                      0 |
+------------------------+
1 row in set, 3 warnings (0.000 sec)

Let’s check the warning:

SHOW WARNINGS;

Result:

+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1292 | Truncated incorrect INTEGER value: 'Two' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'Ten'  |
| Warning | 1292 | Truncated incorrect INTEGER value: 'Two' |
+---------+------+------------------------------------------+

Invalid Argument Count

Calling TRUNCATE() with the wrong number of arguments, or without an argument results in an error:

SELECT TRUNCATE();

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ')' at line 1

And:

SELECT TRUNCATE(1, 2, 3);

Result:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ' 3)' at line 1