## ORACLE SQL TUTORIAL : NUMERIC FUNCTIONS

NUMERIC FUNCTIONS
Ø  Abs
Ø  Sign
Ø  Sqrt
Ø  Mod
Ø  Nvl
Ø  Power
Ø  Exp
Ø  Ln
Ø  Log
Ø  Ceil
Ø  Floor
Ø  Round
Ø  Trunk
Ø  Bitand
Ø  Greatest
Ø  Least
Ø  Coalesce
a) ABS

Absolute value is the measure of the magnitude of value.
Absolute value is always a positive number.

Syntax: abs (value)

Ex:
SQL> select abs(5), abs(-5), abs(0), abs(null) from dual;

ABS(5)    ABS(-5)     ABS(0)  ABS(NULL)
---------- ----------    ---------- -------------
5              -5                0

b) SIGN

Sign gives the sign of a value.

Syntax: sign (value)

Ex:
SQL> select sign(5), sign(-5), sign(0), sign(null) from dual;

SIGN(5)   SIGN(-5)    SIGN(0) SIGN(NULL)
----------   ----------     ---------- --------------
1            -1                       0

c) SQRT

This will give the square root of the given value.

Syntax: sqrt (value)    --  here value must be positive.

Ex:
SQL> select sqrt(4), sqrt(0), sqrt(null), sqrt(1) from dual;

SQRT(4)    SQRT(0) SQRT(NULL)    SQRT(1)
----------    ---------- ---------------    ----------
2               0                                                   1

d) MOD

This will give the remainder.

Syntax: mod (value, divisor)

Ex:
SQL> select mod(7,4), mod(1,5), mod(null,null), mod(0,0), mod(-7,4) from dual;

MOD(7,4)   MOD(1,5) MOD(NULL,NULL)   MOD(0,0)  MOD(-7,4)
------------   ----------  ---------------------    ----------- -------------
3               1                                                   0              -3

e) NVL

This will substitutes the specified value in the place of null values.

Syntax: nvl (null_col, replacement_value)

Ex:
SQL> select * from student;              -- here for 3rd row marks value is null

NO NAME      MARKS
--- -------      ---------
1            a         100
2            b          200
3            c

SQL> select no, name, nvl(marks,300) from student;

NO NAME  NVL(MARKS,300)
---  -------  ---------------------
1   a             100
2   b             200
3   c             300

SQL> select nvl(1,2), nvl(2,3), nvl(4,3), nvl(5,4) from dual;

NVL(1,2)   NVL(2,3)   NVL(4,3)   NVL(5,4)
----------    ----------    ----------    ----------
1               2                   4                   5

SQL> select nvl(0,0), nvl(1,1), nvl(null,null), nvl(4,4) from dual;

NVL(0,0)   NVL(1,1) NVL(null,null)  NVL(4,4)
----------    ---------- -----------------   ----------
0              1                                                         4

f) POWER

Power is the ability to raise a value to a given exponent.

Syntax: power (value, exponent)

Ex:
SQL> select power(2,5), power(0,0), power(1,1), power(null,null), power(2,-5) from
dual;

POWER(2,5) POWER(0,0) POWER(1,1) POWER(NULL,NULL) POWER(2,-5)
--------------  --------------  ----- --------- -----------------------  ---------------
32                    1                  1                                                         .03125

g) EXP

This will raise e value to the give power.

Syntax: exp (value)

Ex:
SQL> select exp(1), exp(2), exp(0), exp(null), exp(-2) from dual;

EXP(1)                   EXP(2)                   EXP(0)  EXP(NULL)    EXP(-2)
--------                   ---------                 --------  -------------    ----------
2.71828183  7.3890561          1                          .135335283

h) LN

This is based on natural or base e logarithm.

Syntax: ln (value)        -- here value must be greater than zero which is positive only.

Ex:
SQL> select ln(1), ln(2), ln(null) from dual;

LN(1)      LN(2)      LN(NULL)
-------      -------      ------------
0            .693147181

Ln and Exp are reciprocal to each other.
EXP (3) = 20.0855369
LN (20.0855369) = 3

i) LOG

This is based on 10 based logarithm.

Syntax: log (10, value)                -- here value must be greater than zero which is positive only.

Ex:
SQL> select log(10,100), log(10,2), log(10,1), log(10,null) from dual;

LOG(10,100)  LOG(10,2)  LOG(10,1) LOG(10,NULL)
---------------   -----------   ------------  -----------------
2            .301029996          0

LN (value) = LOG (EXP(1), value)

SQL> select  ln(3), log(exp(1),3) from dual;

LN(3)      LOG(EXP(1),3)
-------      -----------------
1.09861229    1.09861229

j) CEIL

This will produce a whole number that is greater than or equal to the specified value.

Syntax: ceil (value)

Ex:
SQL> select ceil(5), ceil(5.1), ceil(-5), ceil( -5.1), ceil(0), ceil(null) from dual;

CEIL(5)  CEIL(5.1)   CEIL(-5) CEIL(-5.1)    CEIL(0) CEIL(NULL)
---------  -----------    ---------- ------------     --------  --------------
5            6                -5            -5                 0

k) FLOOR

This will produce a whole number that is less than or equal to the specified value.

Syntax: floor (value

Ex:
SQL> select floor(5), floor(5.1), floor(-5), floor( -5.1), floor(0), floor(null) from dual;

FLOOR(5) FLOOR(5.1)  FLOOR(-5) FLOOR(-5.1)   FLOOR(0) FLOOR(NULL)
-----------  -------------   ------------  --------------    -----------  ----------------
5               5                 -5                 -6                   0

l) ROUND

This will rounds numbers to a given number of digits of precision.

Syntax: round (value, precision)

Ex:
SQL> select round(123.2345), round(123.2345,2), round(123.2354,2) from dual;

ROUND(123.2345)  ROUND(123.2345,0) ROUND(123.2345,2) ROUND(123.2354,2)
---------------------   ------------------------  -----------------------  -----------------------
123                          123                            123.23                      123.24

SQL> select round(123.2345,-1), round(123.2345,-2), round(123.2345,-3),
round(123.2345,-4) from dual;

ROUND(123.2345,-1) ROUND(123.2345,-2) ROUND(123.2345,-3) ROUND(123.2345,-4)
------------------------  -------------------------  ------------------------   ------------------------
120                                100                            0                                         0

SQL> select round(123,0), round(123,1), round(123,2) from dual;

ROUND(123,0) ROUND(123,1) ROUND(123,2)
-----------------  -----------------  ----------------
123                   123                                      123

SQL> select round(-123,0), round(-123,1), round(-123,2) from dual;

ROUND(-123,0) ROUND(-123,1) ROUND(-123,2)
------------------  -----------------   -------------------
-123                 -123                    -123

SQL> select round(123,-1), round(123,-2), round(123,-3), round(-123,-1), round(-123,-
2), round(-123,-3) from dual;

ROUND(123,-1) ROUND(123,-2) ROUND(123,-3) ROUND(-123,-1) ROUND(-123,-2)
ROUND(-123,-3)
------------- ------------- ------------- -------------- -------------- --------------
120           100             0               -120               -100              0

SQL> select round(null,null), round(0,0), round(1,1), round(-1,-1), round(-2,-2) from
dual;

ROUND(NULL,NULL) ROUND(0,0) ROUND(1,1) ROUND(-1,-1) ROUND(-2,-2)
-----------------------  --------------  --------------  ----------------  ----------------
0                       1                   0                    0

m) TRUNC

This will truncates or chops off digits of precision from a number.

Syntax: trunc (value, precision)

Ex:
SQL> select trunc(123.2345), trunc(123.2345,2), trunc(123.2354,2) from dual;

TRUNC(123.2345) TRUNC(123.2345,2) TRUNC(123.2354,2)
---------------------  -----------------------  -----------------------
123                        123.23                     123.23

SQL> select trunc(123.2345,-1), trunc(123.2345,-2), trunc(123.2345,-3),
trunc(123.2345,-4) from dual;

TRUNC(123.2345,-1) TRUNC(123.2345,-2) TRUNC(123.2345,-3) TRUNC(123.2345,-4)
------------------------  ------------------------   -----------------------  ------------------------
120                              100                                0                             0

SQL> select trunc(123,0), trunc(123,1), trunc(123,2) from dual;

TRUNC(123,0) TRUNC(123,1) TRUNC(123,2)
----------------   ----------------  -----------------
123                    123                 123

SQL> select trunc(-123,0), trunc(-123,1), trunc(-123,2) from dual;

TRUNC(-123,0) TRUNC(-123,1) TRUNC(-123,2)
-----------------   -----------------  -----------------
-123                    -123                -123

SQL> select trunc(123,-1), trunc(123,-2), trunc(123,-3), trunc(-123,-1), trunc(-123,2),
trunc(-123,-3) from dual;

TRUNC(123,-1) TRUNC(123,-2) TRUNC(123,-3) TRUNC(-123,-1) TRUNC(-123,2) TRUNC(-
123,-3)
------------- ------------- ------------- -------------- ------------- --------------
120           100             0                   -120          -123              0

SQL> select trunc(null,null), trunc(0,0), trunc(1,1), trunc(-1,-1), trunc(-2,-2) from dual;

TRUNC(NULL,NULL) TRUNC(0,0) TRUNC(1,1) TRUNC(-1,-1) TRUNC(-2,-2)
-----------------------  -------------  -------------  ---------------  ----------------
0                   1                     0                      0

n) BITAND

This will perform bitwise and operation.

Syntax: bitand (value1, value2)

Ex:
SQL> select bitand(2,3), bitand(0,0), bitand(1,1), bitand(null,null), bitand(-2,-3) from
dual;
BITAND(2,3) BITAND(0,0) BITAND(1,1) BITAND(NULL,NULL) BITAND(-2,-3)
--------------  ---------------  --------------   ------------------------  -----------------
2                    0              1                                                                              -4

o) GREATEST

This will give the greatest number.

Syntax: greatest (value1, value2, value3 … valuen)

Ex:
SQL> select greatest(1, 2, 3), greatest(-1, -2, -3) from dual;

GREATEST(1,2,3) GREATEST(-1,-2,-3)
--------------------  -----------------------
3                                -1

Ø  If all the values are zeros then it will display zero.
Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.

p) LEAST

This will give the least number.

Syntax: least (value1, value2, value3 … valuen)

Ex:
SQL> select least(1, 2, 3), least(-1, -2, -3) from dual;

LEAST(1,2,3)         LEAST(-1,-2,-3)
--------------------  -----------------------
1                                -3
Ø  If all the values are zeros then it will display zero.
Ø  If all the parameters are nulls then it will display nothing.
Ø  If any of the parameters is null it will display nothing.
q) COALESCE

This will return first non-null value.

Syntax: coalesce (value1, value2, value3 … valuen)

Ex:
SQL> select coalesce(1,2,3), coalesce(null,2,null,5) from dual;

COALESCE(1,2,3) COALESCE(NULL,2,NULL,5)
-------------------   -------------------------------
1                                                2

### 1 Responses to “ORACLE SQL TUTORIAL : NUMERIC FUNCTIONS”

Unknown said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)