How the TRUNCATE() function works in Mariadb?

The TRUNCATE() function in MariaDB is used to truncate a number to a specified number of decimal places. It removes the digits after the specified number of decimal places, effectively shortening the value.

Syntax

The syntax for the MariaDB TRUNCATE() function is as follows:

TRUNCATE(X, D)

• X: The input numeric value to be truncated.
• D: The number of decimal places to retain. If D is 0, the result will have no decimal point or fractional part. If D is negative, it causes D digits left of the decimal point of the value X to become zero.

The function returns the truncated numeric value.

Examples

Example 1: Truncating a Positive Number

This example demonstrates how to truncate a positive number to a specified number of decimal places.

DROP TABLE IF EXISTS example;
CREATE TABLE example (num DECIMAL(10,5));
INSERT INTO example VALUES (3.14159265359), (1234.56789);

SELECT num, TRUNCATE(num, 3) AS truncated FROM example;

Output:

+------------+-----------+
| num        | truncated |
+------------+-----------+
|    3.14159 |     3.141 |
| 1234.56789 |  1234.567 |
+------------+-----------+

The TRUNCATE() function truncated the numbers to 3 decimal places.

Example 2: Truncating with Zero Decimal Places

This example shows how TRUNCATE() handles truncation with zero decimal places.

DROP TABLE IF EXISTS example;
CREATE TABLE example (num DECIMAL(10,5));
INSERT INTO example VALUES (3.14159265359), (1234.56789);

SELECT num, TRUNCATE(num, 0) AS truncated FROM example;

Output:

+------------+-----------+
| num        | truncated |
+------------+-----------+
|    3.14159 |         3 |
| 1234.56789 |      1234 |
+------------+-----------+

When D is set to 0, the TRUNCATE() function removes the decimal point and fractional part, effectively truncating the numbers to integers.

Example 3: Truncating with Negative Decimal Places

This example demonstrates the behavior of TRUNCATE() when D is negative.

DROP TABLE IF EXISTS example;
CREATE TABLE example (num DECIMAL(10,5));
INSERT INTO example VALUES (3.14159265359), (1234.56789);

SELECT num, TRUNCATE(num, -2) AS truncated FROM example;

Output:

+------------+-----------+
| num        | truncated |
+------------+-----------+
|    3.14159 |         0 |
| 1234.56789 |      1200 |
+------------+-----------+

When D is negative, it causes D digits left of the decimal point of the value X to become zero. In this case, D is set to -2, so the function truncates the numbers to the nearest hundred.

Example 4: Truncating a Negative Number

This example shows how TRUNCATE() handles negative numbers.

DROP TABLE IF EXISTS example;
CREATE TABLE example (num DECIMAL(10,5));
INSERT INTO example VALUES (-3.14159265359), (-1234.56789);

SELECT num, TRUNCATE(num, 2) AS truncated FROM example;

Output:

+-------------+-----------+
| num         | truncated |
+-------------+-----------+
|    -3.14159 |     -3.14 |
| -1234.56789 |  -1234.56 |
+-------------+-----------+

The TRUNCATE() function truncated the negative numbers to 2 decimal places.

Example 5: Truncating with a Larger Decimal Place

This example demonstrates what happens when the specified number of decimal places is larger than the input value.

DROP TABLE IF EXISTS example;
CREATE TABLE example (num DECIMAL(10,5));
INSERT INTO example VALUES (3.14159265359), (1234.56789);

SELECT num, TRUNCATE(num, 10) AS truncated FROM example;

Output:

+------------+-----------------+
| num        | truncated       |
+------------+-----------------+
|    3.14159 |    3.1415900000 |
| 1234.56789 | 1234.5678900000 |
+------------+-----------------+

When the specified number of decimal places is larger than the input value, the TRUNCATE() function returns the original value without truncation.

Related Functions

The following are some functions related to the MariaDB TRUNCATE() function:

• MariaDB ROUND() function is used to round a numeric value to a specified number of decimal places.
• MariaDB FLOOR() function returns the largest integer value that is less than or equal to the input numeric value.
• MariaDB CEIL() function returns the smallest integer value that is greater than or equal to the input numeric value.

Conclusion

The TRUNCATE() function in MariaDB is a powerful tool for truncating numeric values to a specified number of decimal places. By understanding the syntax and usage examples, you can effectively utilize this function in your SQL queries and data manipulation tasks. Whether you need to remove fractional parts, round to specific decimal places, or handle negative decimal places, the TRUNCATE() function provides a convenient way to control the precision of your numeric data.