NUMERIC Functions

ABS Function:
The ABS function returns the absolute value of a numeric expression. It’s useful for converting negative numbers to positive ones.


SELECT ABS(-15) AS Absolute_Value FROM dual;
— Absolute_Value
— 15
CEIL and FLOOR Functions:
The CEIL function rounds a number up to the nearest integer, while the FLOOR function rounds it down to the nearest integer.


SELECT CEIL(5.2) AS Ceiling, FLOOR(5.8) AS Floor FROM dual;
— Result: Ceiling Floor
— 6 5

ROUND Function:
The ROUND function is used to round a numeric value to a specified number of decimal places.

SELECT ROUND(9.876, 2) AS Rounded FROM dual;
— Result: Rounded
— 9.88
TRUNC Function:
The TRUNC function is used to truncate a numeric value to a specified number of decimal places.

SELECT TRUNC(9.876, 2) AS Truncated FROM dual;
— Result: Truncated
— 9.87
MOD Function:
The MOD function returns the remainder when one number is divided by another. It’s useful for finding factors or checking divisibility.

SELECT MOD(15, 7) AS Remainder FROM dual;
— Result: Remainder
— 1
POWER Function:
The POWER function raises a number to a specified exponent.

SELECT POWER(2, 3) AS Result FROM dual;
— Result: Result
— 8

SQRT Function:
The SQRT function calculates the square root of a number.

SELECT SQRT(16) AS Square_Root FROM dual;
— Result: Square_Root
— 4

EXP and LN Functions:
The EXP function returns the exponential value of a number, while the LN function returns the natural logarithm of a number.

SELECT EXP(2) AS Exponential, LN(2.71828) AS Natural_Log FROM dual;
— Result: Exponential 7.3890561

–Natural_Log    1

SIGN Function:
The SIGN function returns the sign of a number as -1 for negative, 0 for zero, and 1 for positive.

SELECT SIGN(-5) AS Sign_Negative, SIGN(0) AS Sign_Zero, SIGN(10) AS Sign_Positive FROM dual;
— Result: Sign_Negative Sign_Zero Sign_Positive
— -1 0 1

The RANDOM function generates a random number between 0 and 1, while the DBMS_RANDOM package provides a more customizable way to generate random numbers.
Example (RANDOM):

— Result: Random_Value
— 0.123456789
Oracle SQL’s numeric functions offer a wide range of tools for performing mathematical operations and calculations on numeric data. Whether you need to round, truncate, calculate square roots, or generate random numbers, these functions can simplify your SQL queries and help you achieve your data analysis goals. By mastering these functions, you’ll be well-equipped to work with numeric data in Oracle SQL.

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top