SQL TRUNCATE Function

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.

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) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result
--------
 123.45Code language: CSS (css)

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

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) result;Code language: SQL (Structured Query Language) (sql)

Try it

Output:

 result
--------
    100

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

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)

Try it

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

Output:

 department_name  | average_salary
------------------+----------------
 Purchasing       |           4150
 Administration   |           4400
 IT               |           5760
 Shipping         |           5885
 Human Resources  |           6500
 Finance          |           8600
 Marketing        |           9500
 Sales            |           9616
 Public Relations |          10000
 Accounting       |          10150
 Executive        |          19333Code language: PHP (php)

Summary #

• Use the SQL TRUNCATE() function to truncate a number to a specified number of decimals.