SQL Usefull Functions

-->SUBSTRINGSELECT SubStr('Shiva_Rama_Krishna',0) FROM d;
SELECT SubStr('Shiva_Rama_Krishna',11) FROM d;
SELECT SubStr('Shiva_Rama_Krishna',122) FROM d;
SELECT SubStr('Shiva_Rama_Krishna',3,11) FROM d;
SELECT SubStr('Shiva_Rama_Krishna',9,-1) FROM d;
SELECT SubStr('Shiva_Rama_Krishna',-4,6) FROM d;
SELECT ename,job,substr(job,6) FROM emp WHERE substr(job,4,2)=upper('es');
SELECT Concat(InitCap(ename),Concat(' is a ',Concat(SubStr(job,1,3),' Eater'))) FROM emp WHERE SubStr(job,4,3)=Upper('age');

-->LENGTHSELECT Length('Raju.chinthapatla') "My NAME Length is" FROM d;
SELECT InitCap(Ename),job FROM e WHERE SubStr(job,4,length(SubStr(job,4,3)))= Upper('age');

-->INSTRINGSELECT InStr('Shiva-Rama-Krishma','a',1,1) from d;
SELECT InStr(job,'A',3,1) FROM emp WHERE job=Upper('manager');
SELECT InStr(job,'A',3) FROM emp WHERE job=Upper('manager');
SELECT InStr(job,'A') FROM emp WHERE job=Upper('manager');

-->RPAD & LPADSELECT RPad(LPad('Raju.ch',10,'*'),13,'*') FROM d;
SELECT lPad(rPad('Raju.ch',15,'*'),23,'*') FROM d;
SELECT LPad('Raju.Ch',20) FROM d;
SELECT rPad('Raju.Ch',3) FROM d;

-->RTRIM & LTRIM & TRIMSELECT LTrim('Ch.Raju','Ch.') FROM d;
SELECT RTrim('Raju.Ch','.ch') FROM d;
SELECT LTrim('raju','') FROM d;
SELECT Trim('s' FROM 'smisths') FROM d;

-->POWERSELECT Power(5,4) FROM d;
SELECT Power(5,-4) FROM d;

-->SQROOTSELECT sqrt(100) FROM d;

-->ABSOLUTESELECT abs(-100) FROM d;
SELECT sal,comm,sal-comm,Abs(sal-comm) FROM e;

-->SIGNSELECT Sign(-100), Sign(100), Sign(0) FROM d;

-->ROUNDSELECT SYSDATE,SYSDATE+10/2 FROM d;
SELECT ename,hiredate,Round((SYSDATE-hiredate)/365) FROM e;

-->ADD_MONTHSSELECT SYSDATE,Add_Months(SYSDATE,1) FROM d;

-->MONTHS_BETWEENSELECT ename,SYSDATE,hiredate,round(Months_Between(SYSDATE,hiredate)/12) " Years Of Experience" FROM e;

-->NEXT_DAYSELECT SYSDATE,Next_Day(SYSDATE,'TUE') FROM d;

--> LAST_DAY <--SELECT SYSDATE,Add_Months(Last_Day(SYSDATE),-1)+1 FROM d;

-->TRUNCSELECT Round(sysdate,'YY'),Trunc(SYSDATE,'MM') FROM d;

--> T0_CHAR NUMBER CONVERTION <--
--Digit MakerSELECT 12.34,12,To_Char(12.34-12,'009900'),To_Char(12-12.34,'009900') FROM d;                      
SELECT 12.55,12,To_Char(12.55-12,'009900'),To_Char(12-12.55,'009900') FROM d;

--> T0_CHAR DATE CONVERTION <--SELECT 123456789,
To_Char(123456789,'L99G99G99G999D99')"Currency,GROUP,Decimal",
To_Char(123456789,'$99,99,99,999.99')"Currency,GROUP,DECIMAL SYMBOLS",
To_Char(123456789,'xxxxxxxxx')"HexaDecimals",
To_Char(000056789,'000099999')"Zero Indicator"
FROM d;

SELECT comm,sal,comm-sal,
To_Char(comm-sal,'99,999.99mi')," MINUS "
To_Char(comm-sal,'99,999.99pr')"Nagative"
FROM e;

-->SIGNSELECT comm,sal,comm-sal,To_Char(comm-sal,'s99,999.99') FROM e;   

--> T0_CHAR DATE CONVERTION <--
-->A.D. OR B.C.//AD OR BCSELECT hiredate,
To_Char(hiredate,'b.c.')"Before Crist",
To_Char(hiredate,'ad')"After Death",
To_Char(hiredate,'a.d.')"After Death",
To_Char(sysdate,'AM')"AM",
To_Char(sysdate,'PM')"PM"
FROM e;

SELECT sysdate,
To_Char(sysdate,'cc-ad')"CENTURY",
To_Char(sysdate,'d')"DAy IN WEEK",
To_Char(sysdate,'dd')"DAY IN MONTH",
To_Char(sysdate,'ddd')"DAY IN YEAR",
To_Char(sysdate,'dddd')"DAY In YEAR and IN WEEK",
To_Char(sysdate,'DY')"DAY SPELL(sun)",
To_Char(sysdate,'DAY')"DAY SPELL",
To_Char(sysdate,'W')"WEEK OF THIS MONTH",
To_Char(sysdate,'IW')"(WI)WEEK OF THIS YEAR",
To_Char(sysdate,'WW')"(WW)WEEK OF THIS YEAR",
To_Char(sysdate,'WWW')"WEEK OF THIS YEAR AND MONTH",
To_Char(sysdate,'MM')"MONTH NO",
To_Char(sysdate,'MON')"MONTH SPELL(jan)",
To_Char(sysdate,'MONTH')"MONTH SPELL",
To_Char(sysdate,'y')"LAST DIGIT OF THE YEAR",
To_Char(sysdate,'yy')"LAST 2 DIGIT OF THE YEAR",
To_Char(sysdate,'yyy')"LAST 3 DIGIT OF THE YEAR",
To_Char(sysdate,'yyyy')"4 DIGIT OF THE YEAR",
To_Char(sysdate,'YYYYSP')"YEAR SPELL",
To_Char(sysdate,'YEAR')"YEAR SPELL",
To_Char(sysdate,'DD-MM-YYYY')"DATE",
To_Char(sysdate,'DD-MON-YYYY')"DATE",
To_Char(sysdate,'DD-RM-YYYY')"ROMAN NUMBER",
To_Char(sysdate,'Q')"QUARTER OF THE YEAR",
To_Char(sysdate,'J')"JULLIAN",
To_Char(sysdate,'HH-MI-SS:AM')"12 HOURS TIME",
To_Char(sysdate,'HH24-MI-SS:AM')"24 HOURS TIME",
To_Char(sysdate,'DDth-DDthsp')"DAY NUMBER SPELL",
To_Char(SYSDATE,'DDthsp-MONTH-YYYYsp')"DATE SPELL",
To_Char(SYSDATE,'fmDDthsp-MONTH-YYYYsp')"REMOVE THE BLANK SPASES"
FROM d;

SELECT sysdate,To_Char(sysdate,'cc-ad')"CENTURY" FROM d;
SELECT sysdate,To_Char(sysdate,'d')"DAy IN WEEK" FROM d;
SELECT sysdate,To_Char(sysdate,'dd')"DAY IN MONTH" FROM d;
SELECT sysdate,To_Char(sysdate,'ddd')"DAY IN Y    EAR" FROM d;
SELECT sysdate,To_Char(sysdate,'dddd')"DAY In YEAR and IN WEEK" FROM d;
SELECT sysdate,To_Char(sysdate,'DY')"DAY SPELL(sun)" FROM d;
SELECT sysdate,To_Char(sysdate,'DAY')"DAY SPELL" FROM d;
SELECT sysdate,To_Char(sysdate,'W')"WEEK OF THIS MONTH" FROM d;
SELECT sysdate,To_Char(sysdate,'IW')"(WI)WEEK OF THIS YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'WW')"(WW)WEEK OF THIS YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'WWW')"WEEK OF THIS YEAR AND MONTH" FROM d;
SELECT sysdate,To_Char(sysdate,'MM')"MONTH NO" FROM d;
SELECT sysdate,To_Char(sysdate,'MON')"MONTH SPELL(jan)" FROM d;
SELECT sysdate,To_Char(sysdate,'MONTH')"MONTH SPELL" FROM d;
SELECT sysdate,To_Char(sysdate,'y')"LAST DIGIT OF THE YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'yy')"LAST 2 DIGIT OF THE YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'yyy')"LAST 3 DIGIT OF THE YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'yyyy')"4 DIGIT OF THE YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'YYYYSP')"YEAR SPELL" FROM d;
SELECT sysdate,To_Char(sysdate,'YEAR')"YEAR SPELL" FROM d;
SELECT sysdate,To_Char(sysdate,'DD-MM-YYYY')"DATE" FROM d;
SELECT sysdate,To_Char(sysdate,'DD-MON-YYYY')"DATE" FROM d;
SELECT sysdate,To_Char(sysdate,'DD-RM-YYYY')"ROMAN NUMBER" FROM d;
SELECT sysdate,To_Char(sysdate,'Q')"QUARTER OF THE YEAR" FROM d;
SELECT sysdate,To_Char(sysdate,'J')"JULLIAN" FROM d;
SELECT sysdate,To_Char(sysdate,'HH-MI-SS:AM')"12 HOURS TIME" FROM d;
SELECT sysdate,To_Char(sysdate,'HH24-MI-SS:AM')"24 HOURS TIME" FROM d;
SELECT sysdate,To_Char(sysdate,'DDth-DDthsp')"DAY NUMBER SPELL" FROM d;
SELECT sysdate,To_Char(SYSDATE,'DDthsp-MONTH-YYYYsp')"DATE SPELL" FROM d;
SELECT sysdate,To_Char(SYSDATE,'fmDDthsp-MONTH-YYYYsp')"REMOVE THE BLANK SPASES" FROM d;

--> TRANSLATE ONE CHARACTER TO ANOTHER CHARACTERSELECT 'raju.chinthapatla',translate('raju.chinthapatla','abcdefghijklmnopqrstuvwxyz.','1234567890!@#$%^&*()-=_+:,<>')" Encrypted NAME " FROM d;

SELECT '*10-.389$)81^1)@1'" Encrypted NAME ",Translate('*10-<389$)81^1)@1','1234567890!@#$%^&*()-=_+:,<>','abcdefghijklmnopqrstuvwxyz.')" Encrypted NAME " FROM d;

--> HAVING CLAUS                                                                           SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e WHERE job='CLERK' GROUP BY deptno HAVING min(sal)<1000;                                                                                                        
SELECT deptno,Sum(sal),Min(sal),Max(sal) FROM e GROUP BY deptno HAVING Count(deptno)>=1;
SELECT deptno,Count(deptno) FROM e GROUP BY deptno HAVING Count(deptno)>3;

--> NESTING OF GROUP FUNCTIONSSELECT sum(min(sal)) FROM e GROUP BY sal;

--> GREATEST and LEAST FUNCTION:-SELECT greatest('D','f','e','f','d','e','f','V','h','V','r')  from d;
SELECT least('D','f','e','f','d','e','f','V','h','V','r')  from d;

SELECT USER,UID FROM d;
SELECT username,user_id  FROM all_users;
SELECT UserEnv('isdba') FROM d;
SELECT UserEnv('language') FROM d;                                               
SELECT UserEnv('terminal') FROM d;
SELECT UserEnv('sessionid') FROM d;
SELECT UserEnv('lang') FROM d;
SELECT UserEnv('instance') FROM d;
SELECT INSTANCE_name FROM v$instance;

CREATE TABLE test(NAME VARCHAR2(20),dpt NUMBER(2));
insert INTO test VALUES('raj',10);
insert INTO test VALUES('raju',20);
insert INTO test VALUES('rajkumar',30);
 select * FROM test;
 SELECT NAME,VSize(NAME) FROM test;
 UPDATE test SET NAME='ALLEN' WHERE NAME='SMITH';
 UPDATE test SET NAME='ALLEN' WHERE dpt=10;
ALTER TABLE test MODIFY NAME CHAR(20);
ALTER TABLE test MODIFY NAME varCHAR(20);

 DELETE test WHERE NAME='raju';