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
1
If all the values are zeros then it will display zero.
2
If all the parameters are nulls then it will display nothing.
3
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
4
If all the values are zeros then it will display zero.
5
If all the parameters are nulls then it will display nothing.
6
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
STRING
FUNCTIONS
18 Initcap
19 Upper
20 Lower
21 Length
22 Rpad
23 Lpad
24 Ltrim
25 Rtrim
26 Trim
27 Translate
28 Replace
29 Soundex
30 Concat
( ‘ || ‘ Concatenation operator)
31 Ascii
32 Chr
33 Substr
34 Instr
35 Decode
36 Greatest
37 Least
38 Coalesce
a) INITCAP
This will capitalize the initial letter of
the string.
Syntax:
initcap (string)
No comments:
Post a Comment