Oracle functions

oracle functions notes.sql

select initcap('assasa') from dual;
select substr('asasda',-6,3) from dual;
select concat('concat','works') from dual;
select trim('   asa   ') from dual;
select trim(Leading 'a' from 'aaaadfdfaaa') from dual;
select trim(Trailing 'a' from 'aaaadfdfaaa') from dual;
select trim(both 'a' from 'aaaadfdfaaa') from dual;
select instr('12343','3',3,2) from dual;
select lpad('asfd',10,'$'),rpad('dad',10,'%') from dual;
select replace('1234','1234','9') from dual;
select nvl(null,'this is substitution for null') from dual;
select nvl2('','true','false') from dual; -- false
select decode('1','1','true','false') from dual; -- true
select decode('4','1','true','2','true2','3','true3','no one matched with me') from dual; --no one matched with me
select decode('3','1','true','2','true2','3','true3','no one matched with me') from dual; -- true3

select to_char(to_date('10-08-99','dd-mm-rr'),'yyyy') from dual;  -- 1999
select to_char(to_date('10-08-50','dd-mm-rr'),'yyyy') from dual; --1950
select to_char(to_date('10-08-49','dd-mm-rr'),'yyyy') from dual;  --2049
select to_char(to_date('10-08-99','dd-mm-yy'),'yyyy') from dual; -- 2099
SELECT TO_CHAR(SYSDATE,'Ddthsp "of" Month Year') FROM DUAL; --Tenth of September Twenty Eighteen
select to_char(sysdate,'yyyy') from dual; --2018

--number function
--take number and return number

--1 round function

SELECT ROUND(10.5) FROM DUAL; --if you didnt sepecify decimal places , then round without decimal 

SELECT ROUND(150.49) FROM DUAL;--if you didnt sepecify decimal places , then round without decimal 

SELECT ROUND(10.48, 1) FROM DUAL;

SELECT ROUND(10.499, 1) FROM DUAL;

SELECT ROUND(10.499, 2) FROM DUAL;

SELECT ROUND(10.593, 2) FROM DUAL;

SELECT ROUND(55.993, 1) FROM DUAL;

SELECT ROUND(55.993, -1) FROM DUAL;

SELECT ROUND(55.493, -2) FROM DUAL;

SELECT ROUND(555.493, -2) FROM DUAL;

SELECT ROUND(570.493, -3) FROM DUAL;

SELECT ROUND(470.493, -3) FROM DUAL;

SELECT ROUND(1470.493, -2) FROM DUAL;

--2  trunc function

SELECT TRUNC(10.6565) FROM DUAL;

SELECT TRUNC(10.6565, 2) FROM DUAL;

SELECT TRUNC(998.6565, -2) FROM DUAL;

SELECT TRUNC(9998.6565, -2) FROM DUAL;

SELECT TRUNC(998.6565, -3) FROM DUAL;

--3 MOD FUNCTION
--Return the remainder of devision

SELECT MOD(15,2) FROM DUAL;

SELECT MOD(15,3) FROM DUAL;

--the mod function is often use to know if the number id odd or even by divided by 2 

SELECT MOD(100,2) FROM DUAL; -- if return 0 then even

SELECT MOD(101,2) FROM DUAL; -- if return non zero value  then odd

select round(sysdate,'year'),trunc(sysdate,'year') from  dual;