FUNCTIONS
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