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. IfD
is negative then theTRUNCATE()
function causesD
digits left of the decimal point ofX
to become zero. In caseD
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.