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

+ ve – ve No Sign
56.89 56.89 56.89

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

+ ve – ve <1 <0 zeros
-56 57 1 0 0

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

COS(0) COS(60) COS(120) COS(180)
1 -.95241298041 .814180970526 -.5984600690

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

EXP(3) EXP(4) EXP(-6) EXP(7.5) EXP(-1.2)
20.08553692 54.598150033 .0024787521 1808.0424144560 .3011942119122

 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

+ ve – ve <1 <0 zeroes
-57 56 0 -1 0

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

LOG(2,64) LOG(5,125) LOG(10,100)
5.99999999999 2.99999999999 2

 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

MOD(30,7)
2

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

POWER(30,7) POWER(3,2) POWER(2,3)
21870000000 9 8

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

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)
12346 12345.7 12345.68 12345.679 12350 12300 12000

Function and Arguments Output Explanation
ROUND(12345.6789,0) 12346 Second argument 0 indicates that first argument is to be rounded to zero digits after decimal. .6789 after decimal is >.5 so to round the digit before decimal is incremented by 1
ROUND(12345.6789,1) 12345.7 Second argument 1 indicates that first argument is to be rounded to one digit after decimal. .789 6 is >.5 so to remove all the digits after digit 6 it is incremented by 1 (7)
ROUND(12345.6789,2) 12345.68 Second argument 2 indicates that first argument is to be rounded to two digits after decimal. .89 after decimal 67 is >.5 so to remove all the digits after digit 67, it is incremented by 1 (68)
ROUND(12345.6789,3) 12345.679 Second argument 3 indicates that first argument is to be rounded to three digits after decimal. 9 after decimal 678 is >.5 so to remove all the digits after digit 678, it is incremented by 1 (679)
ROUND(12345.6789,-1) 12350 Second argument -1 indicates that first argument is to be rounded to one digit before decimal. So 5 becomes 0 and 1 is added to previous digit i.e. 4 is changed to 5
ROUND(12345.6789,-2) 12300 Second argument -2 indicates that first argument is to be rounded to two digits before decimal. So 4, 5 are replaced with 0,0 and  3 is unchanged as digit .4 after 3 is <.5
ROUND(12345.6789,-3) 12000 Second Argument-3 indicates that the first argument is to be rounded to three digits before decimal. So 3,4,5 are replaced with 0,0,0 and  3 is unchanged as digit 3 after is .4 <.5

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

+ ve – ve <1 <0 Zeroes
-1 1 1 -1 0

 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

SQRT(9) SQRT(99)
3 9.9498743710661

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

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)
12345 12345.6 12345.67 12345.678 12340 12300 12000

 

Function and Arguments Output Explanation
TRUNC(12345.6789,0) 12345 Second argument 0 indicates that the zero digits must be present after decimal.
TRUNC(12345.6789,1) 12345.6 Second argument 1 indicates that the one digits must be present after decimal.
TRUNC(12345.6789,2) 12345.67 Second argument 2 indicates that the two digits must be present after decimal.
TRUNC(12345.6789,3) 12345.678 Second argument 3 indicates that the three digits must be present after decimal.
TRUNC(12345.6789,-1) 12340 Second argument -1 indicates that the one digit before decimal must be replaced with 0
TRUNC(12345.6789,-2) 12300 Second argument -2 indicates that the two digits before decimal must be replaced with 0
TRUNC(12345.6789,-3) 12000 Second argument -3 indicates that the three digit before decimal must be replaced with 0

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