What is the Numeric Function in Oracle?

Numeric Function in Oracle

Returning numeric values, numeric functions take numeric input (or nonnumeric input that can be implicitly transformed to a numeric datatype). Because they manipulate values row by row, these are categorised as single-value functions. While most numeric functions do not alter the actual data stored in the database, they do alter the value of the column to which they are applied.

Typically, core operations include routines for rounding or determining absolute values as well as the standard arithmetic operators (+, -, *, /).

Function	Definition	Example
`ABS(value)`	Returns the absolute value of $n$.
`ROUND(value, precision)`	Rounds a value to a specified precision (number of places right of the decimal point).
`TRUNC(value, precision)`	Truncates (chops off) digits of precision from a number.
`MOD(value, divisor)`	Returns the remainder left after dividing a value by a divisor (modulus).

To demonstrate the capabilities of the various function types, we will use a sample table named EMPLOYEE_DATA, which includes columns with VARCHAR2, NUMBER, and DATE datatypes.

-- CREATE TABLE
CREATE TABLE EMPLOYEE_DATA (
    EMP_ID      NUMBER(5) NOT NULL,
    LAST_NAME   VARCHAR2(20),
    SALARY      NUMBER(8, 2),
    HIRE_DATE   DATE,
    COMMISSION  NUMBER(4, 2)
);

-- INSERT VALUES
INSERT INTO EMPLOYEE_DATA (EMP_ID, LAST_NAME, SALARY, HIRE_DATE, COMMISSION) VALUES (101, 'Smith', 50000.75, DATE '2023-01-15', 0.10);
INSERT INTO EMPLOYEE_DATA (EMP_ID, LAST_NAME, SALARY, HIRE_DATE, COMMISSION) VALUES (102, 'JONES', 65000, DATE '2023-03-20', 0.15);
INSERT INTO EMPLOYEE_DATA (EMP_ID, LAST_NAME, SALARY, HIRE_DATE, COMMISSION) VALUES (103, 'ADAMS', 40000.33, DATE '2024-06-01', NULL);
COMMIT;

Example: Using ROUND and Calculating Monthly Salary–

The entire pay is rounded to the closest whole figure after the monthly salary is calculated (pay / 12).

SELECT
    LAST_NAME,
    SALARY,
    ROUND(SALARY) AS ROUNDED_SALARY,
    SALARY / 12 AS MONTHLY_SALARY
FROM EMPLOYEE_DATA
WHERE EMP_ID = 101;

-- OUTPUT:
LAST_NAME            SALARY ROUNDED_SALARY MONTHLY_SALARY
-------------------- ------ -------------- --------------
Smith              50000.75          50001     4166.72917

Example: Using MOD

For employee 102, we determine the remaining compensation by dividing it by 1000.

SELECT
    LAST_NAME,
    SALARY,
    MOD(SALARY, 1000) AS REMAINDER
FROM EMPLOYEE_DATA
WHERE EMP_ID = 102;

-- OUTPUT:
LAST_NAME                SALARY  REMAINDER
-------------------- ---------- ----------
JONES                     65000          0

String Functions

Character functions, another name for string functions, are used to alter or provide information about character strings. Both those that explain the string’s properties and those that alter its content fall under this category. Usually, VARCHAR2 is returned if the input parameter is either CHAR or VARCHAR2.

Function	Definition	Example
`UPPER(string)`	Converts every letter in a string into uppercase.
`LOWER(string)`	Converts every letter in a string to lowercase.
`INITCAP(string)`	Capitalizes the first letter of a word or series of words.
`LENGTH(string)`	Tells how long a string is (how many characters it has).
`SUBSTR(char, m, n)`	Clips out a piece of a string, extracting $n$ characters starting at position $m$.
`CONCAT(string1, string2)` or `		`

Example: Using UPPER, LOWER, INITCAP, and LENGTH

We measure name length and use case conversion.

SELECT
    LAST_NAME,
    UPPER(LAST_NAME) AS UP_NAME,
    LOWER(LAST_NAME) AS LO_NAME,
    INITCAP(LOWER(LAST_NAME)) AS INIT_NAME,
    LENGTH(LAST_NAME) AS LEN
FROM EMPLOYEE_DATA
WHERE EMP_ID = 102;

-- OUTPUT:
LAST_NAME UP_NAME LO_NAME INIT_NAME LEN
--------- ------- ------- --------- ---
JONES     JONES   jones   Jones       5

Example: Using SUBSTR and Concatenation (||)

The first two characters of the last name are taken out and combined with the salary in a text format.

SELECT
    LAST_NAME,
    SALARY,
    SUBSTR(LAST_NAME, 1, 2) || ' earns ' || TO_CHAR(SALARY) AS DETAILS
FROM EMPLOYEE_DATA
WHERE EMP_ID = 101;

-- OUTPUT:
LAST_NAME            SALARY DETAILS
-------------------- ------ ---------------------
Smith              50000.75 Sm earns 50000.75

Date Functions

Date and time data are stored in Oracle’s DATE datatype, which is usually rounded to the closest whole second. To carry out computations, extractions, or format conversions, date functions work with DATE values.

Function	Definition	Example
`SYSDATE`	Returns the current operating system date and time.
`ADD_MONTHS(date, count)`	Adds a specified number of months (`count`) to a date.
`MONTHS_BETWEEN(date2, date1)`	Gives the difference between two dates in months (can be fractional).
`EXTRACT(timeunit FROM datetime)`	Extracts a specific portion (like month, year) of a date.
`LAST_DAY(date)`	Returns the date of the last day of the month that the given date is in.

Example: Calculating Employment Length and Future Dates

We calculate how many months have passed from the employee’s hire date (in relation to the current date and time, SYSDATE) and how long it has been since then.

SELECT
    LAST_NAME,
    HIRE_DATE,
    ROUND(MONTHS_BETWEEN(SYSDATE, HIRE_DATE)) AS MONTHS_WORKED,
    ADD_MONTHS(HIRE_DATE, 6) AS DATE_PLUS_6M
FROM EMPLOYEE_DATA
WHERE EMP_ID = 101;

-- OUTPUT (Assuming SYSDATE is 2024-07-25):
LAST_NAME            HIRE_DATE MONTHS_WORKED DATE_PLUS_6M
-------------------- --------- ------------- ------------
Smith                15-JAN-23            18    15-JUL-23

Example: Extracting Date Components

We use EXTRACT to pull the year component from the hire date.

SELECT
    LAST_NAME,
    HIRE_DATE,
    EXTRACT(YEAR FROM HIRE_DATE) AS HIRE_YEAR
FROM EMPLOYEE_DATA
WHERE EMP_ID = 103;

-- OUTPUT:
LAST_NAME            HIRE_DATE  HIRE_YEAR
-------------------- --------- ----------
ADAMS                01-JUN-24       2024

Conversion Functions

Values from one datatype to another are explicitly changed using conversion functions. This is important because, although implicit type conversion may be carried out automatically by many functions (such as string functions), explicit conversion guarantees that values are handled appropriately. The format datatype TO datatype is frequently followed in the name convention (e.g., TO_CHAR).

The most widely used basic conversion functions are as follows:

TO_CHAR: Converts a DATE or NUMBER into a string of characters, frequently with the help of a particular format model.
TO_DATE: Creates a DATE datatype from a NUMBER, CHAR, or VARCHAR2.
TO_NUMBER: The TO_NUMBER function converts a CHAR or VARCHAR2 that solely contains numbers, a decimal point, or a minus sign into a NUMBER.
CAST: This robust, all-purpose conversion technique transitions between built-in or collection types.

Example: Using TO_CHAR for Formatting

The pay number ($99,999.00) is converted into a character string using a currency format model, and the hire date is displayed using a custom date format (YYYY Month DD).

SELECT
    LAST_NAME,
    TO_CHAR(SALARY, '$99,999.00') AS FORMATTED_SALARY,
    TO_CHAR(HIRE_DATE, 'YYYY Month DD') AS FORMATTED_DATE
FROM EMPLOYEE_DATA
WHERE EMP_ID = 101;

-- OUTPUT:
LAST_NAME            FORMATTED_SALARY FORMATTED_DATE
-------------------- ---------------- --------------------
Smith                     $50,000.75 2023 January 15

Example: Using TO_DATE

In this case, we use a complete YYYY-MM-DD specification, which is implicitly handled as a date literal, but we also convert a string literal to a DATE type for comparison purposes (if the default format DD-MON-YY is not sufficient).

SELECT LAST_NAME, HIRE_DATE
FROM EMPLOYEE_DATA
WHERE HIRE_DATE < TO_DATE('2023-03-01', 'YYYY-MM-DD');

-- OUTPUT:
LAST_NAME            HIRE_DATE
-------------------- ---------
Smith                15-JAN-23

Multiple Row Functions

Instead of processing a collection or group of rows row by row, multiple row functions also known as aggregate functions or group functions return a single result row. They are commonly used to compute summaries for subgroups when combined with the GROUP BY clause.

Aggregate functions, with the exception of the COUNT(*) function, have the important characteristic of ignoring NULL values throughout computations.

Function	Definition	Example
`SUM(value)`	Returns the sum of all values for a group of rows.
`AVG(value)`	Returns the average of values for a group of rows.
`MAX(value)`	Returns the maximum value for a group of rows.
`MIN(value)`	Returns the minimum value for a group of rows.
`COUNT(expr)`	Returns the count of non-NULL values for a column.
`COUNT(*)`	Returns the count of all rows.

Example: Calculating Totals and Averages (Handling NULLs)

We determine the average income, total salary, and the number of employees who receive commissions (COUNT(COMMISSION)) as well as the total number of employees (COUNT(*)).

SELECT
    SUM(SALARY) AS TOTAL_SALARY,
    AVG(SALARY) AS AVG_SALARY,
    MAX(SALARY) AS MAX_SALARY,
    COUNT(*) AS TOTAL_EMPLOYEES,
    COUNT(COMMISSION) AS COMM_EMPLOYEES
FROM EMPLOYEE_DATA;

-- OUTPUT:
TOTAL_SALARY AVG_SALARY MAX_SALARY TOTAL_EMPLOYEES COMM_EMPLOYEES
------------ ---------- ---------- --------------- --------------
  155001.08  51667.0267      65000               3              2

Insight on NULL Handling: Notice that TOTAL_EMPLOYEES is 3, as COUNT(*) counts all rows. However, COMM_EMPLOYEES is 2 because the third employee (ADAMS) has a NULL value in the COMMISSION column, and aggregate functions (except COUNT(*)) ignore NULL values. If AVG(COMMISSION) were calculated, the sum of commissions would be divided by 2 (the count of non-null commissions), not 3 (the total number of employees).

Page Content

Tutorials