MySQL TRUNCATE Function

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

The basic syntax of the TRUNCATE in MySQL 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

Numeric Functions Source Table 1

MySQL TRUNCATE Function Example

The MySQL 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.

Deleting Decimal Values Example 1

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

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`;
MySQL TRUNCATE Function Example 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