SQL Single Row Numeric Functions in Oracle

SQL Single Row numeric Functions are helpful when you need to perform financial, mathematical or scientific calculation on data stored in a database.

Here are the inbuilt numeric functions available in Oracle. These are also available in all SQL based DBMS with minor differences.

SQL Single Row Numeric Functions in Oracle

ABS

Absolute function returns the absolute value of the numeric argument. If you pass a numeric argument with a sign (+ or -), the output you get is without the sign.

Syntax

ABS(NumericValue)

Example

Select ABS(-56.89) “+ ve”, ABS(+56.89) “- ve”, ABS(56.89) “No Sign”  FROM Dual;

Output

CEIL

CEIL function is generally known as the ceiling function.  It returns the next higher integer value of the numeric argument. If you pass a negative number with decimal part, CEIL function returns the integer part of the decimal number with negative sign. If you pass a positive decimal number, CEIL function returns the next higher integer value compared to the integer part of the argument.

Syntax

CEIL(NumericValue)

Example

Select CEIL(-56.89) “+ ve”, CEIL(56.89) “- ve”, CEIL(0.56) “<1”, CEIL(-0.56)  “<0” , CEIL(0.00) “zeros” FROM Dual;

Output

In the above example -56.89 next higher integer value is -56 and 56.89 next higher value is 57.  0.56 is “ceiled” to 1 the first integer after 0.

COS

COS function is the trigonometric function of mathematics.  It returns the Cosine value of the numeric argument as degree radians.

Syntax

COS(NumericValue)

Example

Select COS(0), COS(60), COS(120),  COS(180) FROM Dual;

Output

EXP

EXP function is mathematical exponentiation function.  It returns e (natural log) raised to the power the numeric argument passed in function.

Syntax

EXP(NumericValue)

Example

Select EXP(3), EXP(4), EXP(-6), EXP(7.5), EXP(-1.2) FROM Dual;

Output

 FLOOR

FLOOR function is just opposite of CEIL function.  It returns previous small integer of the decimal number passed as argument.

Syntax

FLOOR(NumericValue)

Example

Select FLOOR (-56.89) “+ ve”, FLOOR (56.89) “- ve”, FLOOR (0.56) “<1”, FLOOR (-0.56)  “<0” , FLOOR (0.00) “zeroes” FROM Dual;

Output

In the above example -56.89 next smaller integer value is -57 and 56.89 next smaller value is 56.  0.56 is “floored” to 0 the first integer before 0.56.

LOG

LOG function returns natural log value of a number passed as second argument to the base specified as first argument of this function.

Syntax

LOG(BaseNumber, NumericValue)

Example

Select log(2,128), log(5,45), log(10,100) FROM Dual;

Output

 MOD

MOD function takes two integer values as arguments. It returns the remainder after dividing first number with second number

Syntax

LOG(NumericValue1, NumericValue2)

Example

Select  MOD(30,7) FROM Dual;

Output

POWER

POWER function takes two integer values as arguments. It returns the calculated value after  raising the first number to the power with second number

Syntax

POWER(NumericValue1, NumericValue2)

Example

Select  POWER(30,7), POWER(3,2),POWER(2,3) FROM Dual;

Output

ROUND

ROUND function takes two values as arguments. It returns the rounded value after rounding the first decimal number to decimal digits defined by second integer argument.

Syntax

ROUND(NumericValue1, Precision)

Example

Select  ROUND(12345.6789, 0) , ROUND(12345.6789, 1), ROUND(12345.6789, 2) , ROUND(12345.6789, 3) , ROUND(12345.6789, -1), ROUND(12345.6789, -2) , ROUND(12345.6789, -3)  FROM Dual;

Output

SIGN

SIGN function takes one numeric value as argument. It returns -1 if number is negative, 1 if the number is positive  and 0 if number is 0

Syntax

SIGN(NumericValue1)

Example

Select  SIGN (-56.89) “+ ve”, SIGN (56.89) “- ve”, SIGN (0.56) “<1”, SIGN (-0.56)  “<0” , SIGN (0.00) “Zeroes” FROM Dual;

Output

 SQRT

SQRT function takes one numeric value as argument. It returns square root value of the number.

Syntax

SQRT(NumericValue1)

Example

SELECT SQRT(9), SQRT(99) FROM Dual;

Output

TRUNC

TRUNC function takes two numeric values as argument. First argument is a decimal number and the second is an integer. This function replaces the number of digits after or before the decimal with 0. If second argument is positive, the digits are removed after decimal. If second argument is negative, the digits are removed after before.

Syntax

TRUNC(NumericValue1, TruncDigits)

Example

SELECT TRUNC (12345.6789, 0) , TRUNC (12345.6789, 1), TRUNC (12345.6789, 2) , TRUNC (12345.6789, 3) , TRUNC (12345.6789, -1), TRUNC (12345.6789, -2) , TRUNC (12345.6789, -3)   FROM Dual;

Output

SQL Single Row numeric Functions are versatile and can be nested within each other to do more complex calculations.