Function in oracle

SINGLE ROW FUNCTION

1. Act on each row return in the query

2.Return one result per row

3. Expects one or more user argument

4. May be nested

Character function

LOWER

Select LOWER ('DNAME'), LOWER ('SQL COURSE') FROM DEPT;

------

UPPER

SELECT ENAME FROM EMP WHERE ENAME= UPPER('&ENAME');

------

INITCAP

SELECT INITCAP(DNAME), INITCAP(LOC) FROM DEPT

------

CONCAT

SELECT CONCAT(ENAME,JOB) "JOB FROM EMP WHERE EMPNO=7900;

------

LPAD

SELECT LPAD(DNAME,20,'*'),LPAD(DNAME,20), LPAD(DNAME,20,'.') FROM DEPT;

------

RPAD

SELECT RPAD(DNAME,20,'*') FROM DEPT;

------

SUBSTR

SELECT SUBSTR('ORACLE',2,4),SUBSTR("DNAME",2,4),SUBSTR(DNAME,3) FROM DEPT;

------

INSTR

SELECT DNAME,INSTR(DNAME,'A'),INSTR(DNAME,'ES'),INSTR(DNAME,'C',1,2) FROM DEPT;//3RD ONE 2ND C POSITION

-----

LTRIM

SELECT DNAME,LTRIM(DNAME,'A'),LTRIM(DNAME,'ARS') FROM DEPT;

------

RTRIM

SELECT DNAME,RTRIM(DNAME,'H'),RTRIM(DNAME,'GHS') FROM DEPT;

------

SOUNDEX

SELECT ENAME FROM EMP WHERE SOUNDEX(ENAME)=SOUNDEX('FRED');

------

LENGTH

SELECT LENGTH('SQL COURSE'),LNGTH(DNAME), LENGTH(DEPTNO),LENGTH(DNAME) FROM DEPT;

------

TRANSLATE

SELECT ENAME,TRANSLATE(ENAME,'C','P'),JOB,TRANSLATE(JOB,'AR','IT') FROM EMP WHERE DEPTNO=10;

REPLACE

SELECT JOB,REPLACE(JOB,'SALESMAN','SALESPERSON') jobr,REPLACE(ENAME,'CO','PX') FROM EMP;

Number function

ROUND

SELECT ROUND(45.923,1),ROUND(45.923),ROUND(45.323)ROUND(SAL/32,2)

-------

CEIL

SELECT CEIL(SAL),CEIL(101.76) FROM EMP WHERE SAL BETWEEN 3000 AND 5000;

-------

FLOOR

SELECT FLOOR(15.7) "Floor" FROM DUAL;

-------

POWER

SELECT SAL,POWER(SAL,2),POWER(50,5) FROM EMP WHERE DEPTNO=10;

-------

SQRT

SELECT SAL,SQRT(SAL),SQRT(40) FROM EMP WHERE SAL>2000;

-------

SIGN

SELECT SAL-COMM,SIGN(SAL-COMM) FROM EMP WHERE DEPTNO=30;

SELECT ENAME,SAL,COMM FROM EMP WHERE SIGN(SAL-COMM)=1;

-------

ABS

SELECT SAL-COMM,ABS(SAL-COMM) FROM EMP WHERE DEPTNO=30;

-------

MOD

SELECT SAL,COMM,MOD(SAL,COMM),MOD(100,40) FROM EMP WHERE DEPTNO=30 ORDER BY COMM;

-------

EXP

SELECT EXP(4) "e to the 4th power" FROM DUAL;

-------

TRUNC

SELECT TRUNC(15.79,1) "Truncate" FROM DUAL;

-------

LOG(M,N),SIN(N),SINH(N),TAN(N),TANH(N),COS(N),COS(H)

DATE FUNCTION

GENERAL FORMAT-----> 16-JUN-1985

SYSDATE

SELECT SYSDATE

FROM SYS.DUAL;

---------

ARITHMETIC OPERATION

SELECT HIREDATE,HIREDATE+7 FROM EMP WHERE HIREDATE LIKE '%JUN%';

---------

MONTHS_BETWEEN

SELECT MONTHS_BETWEEN

(TO_DATE('02-02-1995','MM-DD-YYYY'),

TO_DATE('01-01-1995','MM-DD-YYYY') ) "Months"

FROM DUAL;

---------

ADD_MONTHS

SELECT TO_CHAR(

ADD_MONTHS(hiredate,1),

'DD-MON-YYYY') "Next month"

FROM emp

WHERE ename = 'SMITH';

---------

LAST_DAY

SELECT SYSDATE,

LAST_DAY(SYSDATE) "Last",

LAST_DAY(SYSDATE) - SYSDATE "Days Left"

FROM DUAL;

---------

NEXT_DAY

SELECT NEXT_DAY('15-MAR-98','TUESDAY') "NEXT DAY"

FROM DUAL;

---------

TO_CHAR

SELECT TO_CHAR(HIREDATE, 'Month DD, YYYY')

"New date format" FROM emp

WHERE ename = 'BLAKE';

---------

TO_DATE

SELECT TO_DATE(

'January 15, 1989, 11:00 A.M.',

'Month dd, YYYY, HH:MI A.M.')

FROM DUAL;

---------

RR DATE FORMAT

select empno,ename, to_char(hiredate,'dd-mon-yyyy')

from emp

where hiredate

---------

AGREGATE FUNTION OR GROUP FUNCTIONOR MULTIPLE ROW FUNCTION

AVG

SELECT AVG(sal) "Average"

FROM emp;

---------

MAX

SELECT MAX(sal) "Maximum" FROM emp;

---------

SUM

SELECT SUM(sal) "Total"

FROM emp;

---------

COUNT

SELECT COUNT(*) "Total",COUNT(job),COUNT(DISTINCT job) FROM emp;

---------

MIN

SELECT MIN(hiredate) "Earliest" FROM emp;

---------

GROUP BY

SELECT JOB,AVG(SAL)

FROM EMP

GROUP BY JOB;

---------

GROUP UNDER GROUPS

SELECT DEPTNO,JOB,AVG(SAL)

FROM EMP

GROUP BY DEPTNO,JOB;

---------

ERRONEOUS STATEMENT

SELECT DEPTNO,MIN(SAL)

FROM EMP;

---------

HAVING

SELECT DEPTNO,AVG(SAL)

FROM EMP

GROUP BY DEPTNO

HAVING COUNT(*)>3

**************************SOME EXTRA FUNCTION**********************

NVL2 FUNCTION (IF FIRST EXPRESSION IS NULL IT RETURNS SCOND EXPR OTHERWISE 3RD)

SQL>SELECT ENAME,COMM,NVL2(COMM,0,1) FROM EMP;

---------

NULLIF(IF TWO EXPRESSION IS EQUAL IT RETURNS NULL)

SQL>SELECT ENAME, LENGTH(ENAME) EXPR1,LENGTH(JOB) EXPR2,NULLIF(LENGTH(ENAME),LENGTH(JOB)) RESULT FROM EMP;

---------

CASE(IF ELSE IN SQL)

SELECT ENAME, SAL,JOB,

CASE ENAME WHEN 'ALLEN' THEN SAL*10

WHEN 'SMITH' THEN SAL*100

WHEN 'JAMES' THEN SAL*1000

ELSE SAL END "REVISED SALARY"

FROM EMP