SQL TRUNCATE

Summary: in this tutorial, you will learn how to use the SQL TRUNCATE() function truncate a number to a specified number of decimal places.

Overview of SQL TRUNCATE() function

The following shows the syntax of the TRUNCATE() function:

TRUNCATE(n, d)
Code language: SQL (Structured Query Language) (sql)

The TRUNCATE() function returns n truncated to d decimal places. If you skip d, then n is truncated to 0 decimal places. If d is a negative number, the function truncates the number n to d digits left to the decimal point.

The TRUNCATE() function is supported by MySQL. However, MySQL requires both n and d arguments.

Oracle and PostgreSQL provide the TRUNC() function which has the same functionality as the TRUNCATE() function.

SQL Server, however, uses the ROUND() function with the third parameter that determines the truncation operation:

ROUND(n,d, f)
Code language: SQL (Structured Query Language) (sql)

If f is not zero, then the ROUND() function rounds n to the d number of decimal places.

SQL TRUNCATE() function examples

A) Using TRUNCATE function with a positive number of decimal places

The following statement shows how to use the TRUNCATE() function for a positive number:

SELECT TRUNCATE(123.4567,2);
Code language: SQL (Structured Query Language) (sql)

The following shows the output:

In this example, the TRUNCATE() function truncated a number down to two decimal places.

B) Using TRUNCATE() function with a negative number of decimal places

The following example uses the TRUNCATE() function with a negative number of decimal places:

SELECT TRUNCATE(123.4567,-2);
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

In this example, the number of decimal places is, therefore,-2 the TRUNCATE() function truncated two digits left to the decimal points.

C) Using TRUNCATE() function with table columns

The following statement finds the average salary of employees for each department:

SELECT 
    department_name, 
    TRUNCATE(AVG(salary),0) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name
ORDER BY 
    average_salary;;
Code language: SQL (Structured Query Language) (sql)

In this example, we use the TRUNCATE() function to remove all numbers after the decimal points from the average salary.

Here is the output:

In this tutorial, you have learned how to use the SQL TRUNCATE() function to truncate a number to a specified number of decimals.