The TRUNCATE() function in MySQL is used to truncate a number to a specified number of decimal places. This function is similar to the ROUND() function, but it does not perform any rounding. Instead, it simply removes the specified number of digits from the decimal part of the number.
Syntax
The syntax of the TRUNCATE() function is as follows:
TRUNCATE(N, D)
Where:
N is the number to be truncated
D is the number of decimal places to which N should be truncated
If D is 0, then the result will have no decimal place or fractional part. If D is negative, then the function will truncate D digits from the left of the decimal point.
Example
Here are some examples of how to use the TRUNCATE() function:
SELECT TRUNCATE(123.456, 2);
This query will return 123.46.
SELECT TRUNCATE(-123.456, -1);
This query will return -120.
SELECT TRUNCATE(0, 0);
This query will return 0.
The TRUNCATE() function is useful for formatting numbers for presentation or for comparison. It is also a more efficient way to remove decimal places than using the ROUND() function, because it does not require any rounding calculations.
Example of how to use the TRUNCATE() function to format a number for presentation:
SELECT TRUNCATE(price, 2) AS price FROM products;
This query will return the price of each product in the products table, rounded to the nearest two decimal places.
Example of how to use the TRUNCATE() function to compare two numbers:
SELECT * FROM customers WHERE total_purchases > TRUNCATE(AVG(total_purchases), 2);
This query will select all customers who have made total purchases that are greater than the average purchase amount, rounded to the nearest two decimal places.