Trunc в Oracle: функция обрезки строки

Oracle Trunc Function

Trunc is one of the most useful functions in Oracle that is used to truncate values in numeric data or date to a specified level of precision.

Syntax of Trunc function:

SELECT TRUNC(column_name, precision_level)
FROM table_name;

In the above query, column_name is the column from which we want to truncate the value, and precision_level is the level of precision to which we want to truncate the value.

Example of using Trunc function:

Suppose we have a table with a column "salary" which stores employees' salaries. We want to truncate the values to an integer.

CREATE TABLE employees(
   id NUMBER,
   name VARCHAR2(50),
   salary NUMBER(10, 2)
);

INSERT INTO employees (id, name, salary)
VALUES (1, 'John Doe', 10000.50);
INSERT INTO employees (id, name, salary)
VALUES (2, 'Jane Smith', 15000.75);

SELECT TRUNC(salary)
FROM employees;

The above query will display salaries truncated to an integer:

10000
15000

As seen from the example, the Trunc function truncates the decimal part of the number, leaving only the integer value.

Another useful example of using the Trunc function is truncating a date to a specified level of precision, such as year, month, or day.

SELECT TRUNC(hire_date, 'YEAR')
FROM employees;

The above query will display hire dates truncated to the year:

01-JAN-2000
01-JAN-2000

In this example, the Trunc function truncates the dates to the year, ignoring the month and day.

In both examples, we used the Trunc function in the SELECT query, but it can also be used in other contexts such as WHERE conditions or SELECT expressions.

SELECT *
FROM employees
WHERE TRUNC(salary) > 10000;

This query will select all employee records with a salary greater than 10000 (truncated to an integer value).

General code showcasing various uses of the Trunc function:

-- Example 1: Truncating a number to an integer
SELECT TRUNC(10.55) FROM dual;
-- Output: 10

-- Example 2: Truncating a date to the year
SELECT TRUNC('01-JAN-2022', 'YEAR') FROM dual;
-- Output: 01-JAN-2022

-- Example 3: Using Trunc in a SELECT expression
SELECT TRUNC(salary) AS truncated_salary, TRUNC(salary) * 12 AS annual_salary
FROM employees;

This example uses the Trunc function to truncate the salary to an integer value and calculate the annual salary by multiplying it by 12.

Thus, the Trunc function in Oracle provides a lot of possibilities for truncating numeric data or date values to a specified level of precision. Its flexibility and ease of use make it an indispensable tool when working with data in Oracle.