MySQL TRUNCATE Function

MySQL TRUNCATE function is one of the Mathematical Functions which is useful to truncate the specified expression to a user-specified decimal position or length. In this article, we show you, How to get the truncated values using Command Prompt and Workbench with an example.

MySQL TRUNCATE Function Syntax

The basic syntax of the TRUNCATE function is

SELECT TRUNCATE(Numeric_Expression, Decimal_Points)
FROM Source
  • Numeric_Expression: It can be a number or a valid numerical expression.
  • Decimal_Points: This is the precision to which the Expression deletes numbers. It can be a number or a valid numerical expression.
    • If the value is zero, it removes all the decimal numbers from the Numeric_Expression.
    • If it is a positive number, the function keeps the number of decimal values specified by this argument. For example, if the value is 2, then the function allows only 2 decimal places. And the remaining numbers were removed.
    • And If it’s a negative value, the Expression can be removed to the left side of the decimal point.

To demonstrate this MySQL TRUNCATE Numeric function, we are going to use the below-shown data.

Source Table 1

MySQL TRUNCATE Function Example

This Function returns the truncated value to the specified decimal position. The following query shows you multiple ways to use this with positive and negative decimal positions.

-- on Negative 
SELECT TRUNCATE(-20.21458, 2) AS `TruncatedValue`;

-- on Positive
SELECT TRUNCATE(125.5745789, 4) AS `TruncatedValue`;

-- on string data
SELECT TRUNCATE('105.59', 5) AS `TruncatedValue`;

-- on String text
SELECT TRUNCATE('MySQL', 3) AS `TruncatedValue`;

From the below Mathematical method screenshot, you can see we used it on different numbers. Here, we assigned a new name to the MySQL result using the ALIAS Column.

MySQL TRUNCATE Function for Deleting Decimal Values Example

Let me try Negative numbers as the second argument of the MySQL Truncate function.

SELECT TRUNCATE(-2152.21458, -2) AS `TruncatedValue`;

SELECT TRUNCATE(45825.5745789, -3) AS `TruncatedValue`;

SELECT TRUNCATE(986795.59, -1) AS `TruncatedValue`;

SELECT TRUNCATE(145.94589, 0) AS `TruncatedValue`;
Negative Values as second argument 2

The MySQL Numeric Function also allows you to find the truncated values for the column data. In this example, we are going to find the deleted values for all the records present in Sales, Standard Cost, tax Amount, and Service Grade columns.

SELECT Product, Color,
		StandardCost, TRUNCATE(StandardCost, 1) AS Cost, 
        Sales, TRUNCATE(Sales, 2) AS Sales, 
        TaxAmt, TRUNCATE(TaxAmt, 3) AS Tax,
        ServiceGrade, TRUNCATE(ServiceGrade, 0) AS Grade
FROM `numeric functions`;
MySQL TRUNCATE Function Example 3

We will write the same query that we used in our previous example. But, this time, we use negative values as the second argument.

SELECT Product, Color,
		StandardCost, TRUNCATE(StandardCost, -1) AS Cost, 
        Sales, TRUNCATE(Sales, -2) AS Sales, 
        TaxAmt, TRUNCATE(TaxAmt, -1) AS Tax,
        ServiceGrade
FROM `numeric functions`;
MySQL TRUNCATE Function Example 4