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
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
STRING FUNCTIONS
a) INITCAP
This will capitalize the initial letter
of the string.
Syntax: initcap (string)
Ex:
SQL> select
initcap('computer') from dual;
INITCAP
-----------
Computer
b) UPPER
This will convert the string into
uppercase.
Syntax: upper (string)
Ex:
SQL> select
upper('computer') from dual;
UPPER
-----------
COMPUTER
c) LOWER
This will convert the string into
lowercase.
Syntax: lower (string)
Ex:
SQL> select
lower('COMPUTER') from dual;
LOWER
-----------
computer
d) LENGTH
This will give length of the string.
Syntax: length (string)
Ex:
SQL> select length('computer') from dual;
LENGTH
-----------
8
e) RPAD
This will allows you to pad the right
side of a column with any set of characters.
Syntax: rpad (string, length [, padding_char])
Ex:
SQL> select
rpad('computer',15,'*'), rpad('computer',15,'*#') from dual;
RPAD('COMPUTER'
RPAD('COMPUTER'
----------------------
----------------------
computer*******
computer*#*#*#*
--
Default padding character was blank space.
f) LPAD
This will allows you to pad the left
side of a column with any set of characters.
Syntax: lpad (string, length [, padding_char])
Ex:
SQL> select
lpad('computer',15,'*'), lpad('computer',15,'*#') from dual;
LPAD('COMPUTER'
LPAD('COMPUTER'
---------------------
---------------------
*******computer
*#*#*#*compute
--
Default padding character was blank space.
g) LTRIM
This will trim off unwanted characters
from the left end of string.
Syntax: ltrim (string [,unwanted_chars])
Ex:
SQL> select
ltrim('computer','co'), ltrim('computer','com') from dual;
LTRIM(
LTRIM
--------
---------
mputer
puter
SQL> select
ltrim('computer','puter'), ltrim('computer','omputer') from dual;
LTRIM('C
LTRIM('C
----------
----------
computer
computer
--
If you haven’t specify any unwanted characters it will display entire string.
h) RTRIM
This will trim off unwanted characters
from the right end of string.
Syntax: rtrim (string [, unwanted_chars])
Ex:
SQL> select
rtrim('computer','er'), rtrim('computer','ter') from dual;
RTRIM(
RTRIM
--------
---------
comput
compu
SQL> select
rtrim('computer','comput’), rtrim('computer','compute') from dual;
RTRIM('C
RTRIM('C
----------
----------
computer
computer
--
If you haven’t specify any unwanted characters it will display entire string.
i) TRIM
This will trim off unwanted characters
from the both sides of string.
Syntax: trim (unwanted_chars from string)
Ex:
SQL> select
trim( 'i' from 'indiani') from dual;
TRIM(
-----
ndia
SQL> select trim( leading'i' from 'indiani') from
dual; -- this will work as LTRIM
TRIM(L
------
ndiani
SQL> select trim( trailing'i' from 'indiani') from
dual; -- this will work as RTRIM
TRIM(T
------
Indian
j) TRANSLATE
This will replace the set of
characters, character by character.
Syntax: translate (string, old_chars, new_chars)
Ex:
SQL> select
translate('india','in','xy') from dual;
TRANS
--------
xydxa
k) REPLACE
This will replace the set of
characters, string by string.
Syntax: replace (string, old_chars [, new_chars])
Ex:
SQL> select
replace('india','in','xy'), replace(‘india’,’in’) from dual;
REPLACE
REPLACE
-----------
-----------
Xydia
dia
l) SOUNDEX
This will be used to find words that sound
like other words, exclusively used in where
clause.
Syntax: soundex (string)
Ex:
SQL> select *
from emp where soundex(ename) = soundex('SMIT');
EMPNO
ENAME JOB
MGR HIREDATE
SAL DEPTNO
-------- --------
-----
----- ------------ --------- ----------
7369
SMITH
CLERK 7902
17-DEC-80
500 20
m) CONCAT
This will be used to combine two strings
only.
Syntax: concat (string1, string2)
Ex:
SQL> select
concat('computer',' operator') from dual;
CONCAT('COMPUTER'
-------------------------
computer
operator
If you want to combine more than two strings
you have to use concatenation
operator(||).
SQL> select 'how' || ' are' || ' you' from dual;
'HOW'||'ARE
---------------
how are
you
n) ASCII
This will return the decimal representation
in the database character set of the first
character of the string.
Syntax: ascii (string)
Ex:
SQL> select
ascii('a'), ascii('apple') from dual;
ASCII('A')
ASCII('APPLE')
------------
------------------
97 97
o) CHR
This will return the character having the
binary equivalent to the string in either the
database character set or the national
character set.
Syntax: chr (number)
Ex:
SQL> select
chr(97) from dual;
CHR
-----
a
p) SUBSTR
This will be used to extract
substrings.
Syntax: substr (string, start_chr_count [,
no_of_chars])
Ex:
SQL> select
substr('computer',2), substr('computer',2,5), substr('computer',3,7)
from dual;
SUBSTR(
SUBST SUBSTR
----------
------- --------
omputer
omput mputer
Ø If no_of_chars parameter is negative then it will display
nothing.
Ø If both parameters except string are null or zeros then it will display
nothing.
Ø If no_of_chars parameter is greater than the length of the
string then it ignores and calculates based on the orginal string length.
Ø If start_chr_count is negative then it will extract the substring
from right end.
1
2
3
4
5
6
7 8
C
O
M
P
U T
E R
-8 -7 -6
-5
-4
-3
-2 -1
q) INSTR
This will allows you for searching
through a string for set of characters.
Syntax: instr (string, search_str [,
start_chr_count [, occurrence] ])
Ex:
SQL> select
instr('information','o',4,1), instr('information','o',4,2) from dual;
INSTR('INFORMATION','O',4,1)
INSTR('INFORMATION','O',4,2)
------------------------------------
-------------------------------------
4
10
Ø If you are not specifying start_chr_count and occurrence then it will start
search from the beginning and finds first occurrence only.
Ø If both parameters start_chr_count and occurrence are null, it will display
nothing.
r) DECODE
Decode will act as value by value
substitution.
For every value of field, it will checks for
a match in a series of if/then tests.
Syntax: decode (value, if1, then1, if2, then2, …….
else);
Ex:
SQL> select
sal, decode(sal,500,'Low',5000,'High','Medium') from emp;
SAL DECODE
----- ---------
500 Low
2500 Medium
2000 Medium
3500 Medium
3000 Medium
5000 High
4000 Medium
5000 High
1800 Medium
1200 Medium
2000 Medium
2700 Medium
2200 Medium
3200 Medium
SQL> select decode(1,1,3), decode(1,2,3,4,4,6) from
dual;
DECODE(1,1,3)
DECODE(1,2,3,4,4,6)
-----------------
------------------------
3
6
Ø If the number of
parameters are odd and different then decode will display
nothing.
Ø If the number of
parameters are even and different then decode will display last
value.
Ø If all the parameters are
null then decode will display nothing.
Ø If all the parameters are
zeros then decode will display zero.
s) GREATEST
This will give the greatest string.
Syntax: greatest (strng1, string2, string3 …
stringn)
Ex:
SQL> select
greatest('a', 'b', 'c'), greatest('satish','srinu','saketh') from dual;
GREAT
GREAT
-------
-------
c srinu
Ø If all the parameters are
nulls then it will display nothing.
Ø If any of the parameters
is null it will display nothing.
t) LEAST
This will give the least string.
Syntax: greatest (strng1, string2, string3 …
stringn)
Ex:
SQL> select
least('a', 'b', 'c'), least('satish','srinu','saketh') from dual;
LEAST
LEAST
-------
-------
a saketh
Ø If all the parameters are
nulls then it will display nothing.
Ø If any of the parameters
is null it will display nothing.
u) COALESCE
This will gives the first non-null string.
Syntax: coalesce (strng1, string2, string3 …
stringn)
Ex:
SQL> select
coalesce('a','b','c'), coalesce(null,'a',null,'b') from dual;
COALESCE
COALESCE
-----------
-----------
a
a
No comments:
Post a Comment