Functions can be categorized as follows.
Ø Single row functions
Ø Group functions
SINGLE ROW FUNCTIONS
Single row functions can be categorized into five. These will be
applied for each row and produces individual output for each row.
Ø Numeric functions
Ø String functions
Ø Date functions
Ø Miscellaneous functions
Ø Conversion 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
No comments:
Post a Comment