MySQL TRUNCATE Function

Created with Sketch.

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)

Code language: SQL (Structured Query Language) (sql)

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);

Code language: SQL (Structured Query Language) (sql)

Here is the output:

Because the number of decimal places argument is 1, the TRUNCATE() 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)

Code language: SQL (Structured Query Language) (sql)

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);

Code language: SQL (Structured Query Language) (sql)

Here is the query output:

As clearly shown in the output, the TRUNCATE() function only trims the decimal places while the ROUND() 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.

Leave a Reply

Your email address will not be published. Required fields are marked *