Character function , Number function, Date function
Character functions
Ø
Select
LOWER(ename) from emp; it displays letters in lowercase
Ø
Select
UPPER(ename) from emp; it displays letters in uppercase
Ø
Select
INITCAP (ename) from emp; it displays 1st letter capital
Ø
Select
CONCAT (ename, job) from emp; it combines two columns
Ø
Select
SUBSTR(ename,2) from emp; it displays ename from 2nd letter
Ø
Select
SUBSTR(ename,1,3) from emp; it displays ename from 1st letter
to 3rd letter
Ø
Select
LENGTH(ename) from emp;
Ø
Select
ename, INSTR (ename, ‘S’, 2,1) from emp; it displays letter S count from
2nd position of 1st S in the string
Ø
Select
ename, INSTR (ename,’S’,1,2) from emp; it displays letter S count from 1st
position to 2nd S in the string
Ø
Select
ename, INSTR (ename, ‘L’, -2,1) from emp; it displays letter L count
from Last position of 1st L in the string
Ø Select LPAD (ename,20.’@’)
from emp; it displays adding @ to left side of ename to reach char length as 20
Ø Select RPAD (ename,20.’@’)
from emp; it displays adding @ to right side of ename to reach char length as
20
Ø Select LTRIM (ename, ’K’)
from emp; it displays trim letter K from left side of ename
Ø Select RLTRIM (ename, ’K’)
from emp; it displays trim letter K from right side of ename
Ø Select ename, TRIM
(leading/trailing/both ‘K’ from ename) from emp; leading means 1st
letter, trailing means last letter
Ø Select ename, REPLACE (ename,
‘LA’, ‘KP’) from emp; it replays Letter LA with Letter KP in ename
Ø Select ename, TRANSLATE
(ename, ‘LA’, ‘KP’) from emp; it translates letter L with K and A with P in
ename
Ø Select CHR (65) from dual; it
displays character of that given number
Ø Select ASCII (A) from dual;
it displays binary equallant number of that character
Number function
Ø Select ROUND (19.655,2),
ROUND (126.11, -2) from dual; it displays result as 19.66,100
Ø Select TRUNC (19.655,2),
TRUNC (126.11, -2) from dual; it displays result as 19.65,100
Ø Select CEIL (19.001), CEIL
(19), CEIL (-19.561) from dual; it displays result as 20,19,-19
Ø Select FLOOR (19.001), FLOOR
(19), FLOOR (-19.561) from dual; it displays result as 19,19,-20
Ø Select MOD (100,10), MOD
(100,12) from dual; it displays result as 0,4
Ø Select POWER (5,2) from dual;
it displays result as 25
Ø Select SQRT (25) from dual;
it displays result as 5
Ø Select ABS (-36), ABS (0.51),
ABS (15) from dual; it displays result as 36,0.51,15
Ø Select SIGN (10), SIGN (-10)
from dual; it displays result as +1, -1
Date function
Ø Select sysdate, sysdate+1,
sysdate-1, sysdate+1/24, sysdate-1/24 from dual; it displays as +1 result next
date, -1 result previous date, +1/24 result same date after one hour, -2/24
result same date before two hours
Ø Select sysdate, ADD_MONTHS
(sysdate, 2) from dual; it displays adding two months to sysdate
Ø Select MONTHS_BETWEEN
(to_date(’05-JAN-2021’), sysdate) from dual; it displays how many months
between two dates
Ø Select NEXT_DAY (sysdate,
‘TUE’) from dual; it displays next date of given week day
Ø Select LAST_DAY (sysdate)
from dual; it displays last date of given date in the month
Ø Select sysdate, ROUND
(sysdate, ‘day’), ROUND (sysdate, ‘mon’), ROUND (sysdate, ‘year’) from dual;
day result week start date, mon result month starting date, year result year
starting date for the given sysdate. Note: -For day if sysdate is
from thu it displays next week start date, for mon is sysdate is from 16th it
displays next month start date, for year if sysdate is from jul it displays
next year start date.
Ø Select sysdate, TRUNC
(sysdate, ‘day’), TRUNC (sysdate, ‘mon’), TRUNC (sysdate, ‘year’) from dual;
day result week start date, mon result month starting date, year result year
starting date for the given sysdate.
Please watch and subscribe my Channel
Comments
Post a Comment