MySQL TRUNCATE Function

 MySQL TRUNCATE Function

Summary: in this tutorial, you will learn how to use the MySQL TRUNCATE() function to truncate a number to a specified number of decimal places.

MySQL TRUNCATE() function truncates a number to a specified number of decimal places as shown below:

TRUNCATE(X,D)

In this syntax:

• X is a literal number or a numeric expression to be truncated.
• D is the number of decimal places to truncate to. If D is negative then the TRUNCATE() function causes D digits left of the decimal point of X to become zero. In case D is zero, then the return value has no decimal point.

Both X and D arguments are required.

Notice that the TRUNCATE() function is similar to the ROUND() function in terms of reducing the number of decimal places. However, the TRUNCATE() function does not perform any rounding as the ROUND() function does.

MySQL TRUNCATE() function examples

Let’s see some examples of using the TRUNCATE() function.

1) Using MySQL TRUNCATE() with a positive number of decimal places example

See the following example:

SELECT TRUNCATE(1.555,1);

Here is the output:

Because the number of decimal places argument is 1, the TRUNCATE() the function keeps only 1 decimal place in the return value.

2) Using MySQL TRUNCATE() with a negative number of decimal places example

The following example shows how to apply the TRUNCATE() function with a negative number of decimal places:

SELECT
    TRUNCATE(199.99,-2)

The output is:

3) MySQL TRUNCATE() vs. ROUND()

The following example uses both TRUNCATE() and ROUND() function for comparison:

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

Here is the query output:

As clearly shown in the output, the TRUNCATE() function only trims the decimal places while the ROUND() the function performs the rounding.

In this tutorial, you have learned how to use the MySQL TRUNCATE() function to truncate a number to a specified number of decimal places.