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

+ ve– veNo Sign
56.8956.8956.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<0zeros
-5657100

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.0855369254.598150033.00247875211808.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<0zeroes
-57560-10

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.999999999992.999999999992

 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)
2187000000098

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)
1234612345.712345.6812345.679123501230012000

Function and ArgumentsOutputExplanation
ROUND(12345.6789,0)12346Second 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.7Second 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.68Second 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.679Second 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)12350Second 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)12300Second 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)12000Second 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<0Zeroes
-111-10

 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)
39.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)
1234512345.612345.6712345.678123401230012000

 

Function and ArgumentsOutputExplanation
TRUNC(12345.6789,0)12345Second argument 0 indicates that the zero digits must be present after decimal.
TRUNC(12345.6789,1)12345.6Second argument 1 indicates that the one digits must be present after decimal.
TRUNC(12345.6789,2)12345.67Second argument 2 indicates that the two digits must be present after decimal.
TRUNC(12345.6789,3)12345.678Second argument 3 indicates that the three digits must be present after decimal.
TRUNC(12345.6789,-1)12340Second argument -1 indicates that the one digit before decimal must be replaced with 0
TRUNC(12345.6789,-2)12300Second argument -2 indicates that the two digits before decimal must be replaced with 0
TRUNC(12345.6789,-3)12000Second 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.