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.

MySQL TRUNCATE Syntax

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 truncated. It can be a number or a valid numerical expression.
    • If the value is zero, the function removes all the decimal values 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 values truncate.
    • And If its negative value, Expression can be truncated to the left side of the decimal point.

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

MySQL TRUNCATE Function 1

MySQL TRUNCATE Function Example 1

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

-- MySQL TRUNCATE Function Example

-- Truncate Function on Negative Value
SELECT TRUNCATE(-20.21458, 2) AS `Truncated Value`;

-- Truncate Function on Positive Value
SELECT TRUNCATE(125.5745789, 4) AS `Truncated Value`;

-- Truncate Function on string value
SELECT TRUNCATE('105.59', 5) AS `Truncated Value`;

-- Truncate Function on String
SELECT TRUNCATE('MySQL', 3) AS `Truncated Value`;

From the screenshot Mathematical Function below, you can see we used TRUNCATE function on different values. Here, we assigned a new name to the MySQL result using the ALIAS Column.

MySQL TRUNCATE Function Example 1

Let me try Negative Values as the second argument

-- MySQL TRUNCATE Function Example

-- Truncate Function on Negative Value
SELECT TRUNCATE(-2152.21458, -2) AS `Truncated Value`;

-- Truncate Function on Positive Value
SELECT TRUNCATE(45825.5745789, -3) AS `Truncated Value`;

-- Truncate Function Example
SELECT TRUNCATE(986795.59, -1) AS `Truncated Value`;

-- Truncate Function Example
SELECT TRUNCATE(145.94589, 0) AS `Truncated Value`;
MySQL TRUNCATE Function Example 2

MySQL TRUNCATE Function Example 2

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

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

MySQL TRUNCATE 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 for the TRUNCATE Function.

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