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.

Introduction to MySQL TRUNCATE function

The TRUNCATE() function truncates a number to a specified number of decimal places.

Here’s the syntax of the TRUNCATE() function:

TRUNCATE(X,D)Code language: SQL (Structured Query Language) (sql)

In this syntax:

X is a number or a numeric expression that you want to truncate.
D is the number of decimal places that you want to truncate to.

The TRUNCATE() function requires both X and D arguments.

When the D parameter in the TRUNCATE(X, D) function is negative, the function truncates the digits to the left of the decimal point (to the left of the dot) in the number X. This effectively set those leftmost digits to zero.

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 take some examples of using the TRUNCATE() function.

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

The following example uses the TRUNCATE() function to truncate a number to one decimal place:

SELECT TRUNCATE(1.555,1);Code language: SQL (Structured Query Language) (sql)

Output:

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

2) Using the 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)Code language: SQL (Structured Query Language) (sql)

Output:

In this example, we use a negative number of decimal places (-2), therefore, the TRUNCATE() function sets the number 99 (the digits to the left of the decimal point) to zero, resulting in 100.00.

3) TRUNCATE() vs. ROUND() example

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

SELECT 
    TRUNCATE(1.999,1), 
    ROUND(1.999,1);Code language: SQL (Structured Query Language) (sql)

Output:

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

Summary

Use the MySQL TRUNCATE() function to truncate a number to a specified number of decimal places.

Was this tutorial helpful?