Wednesday, October 23, 2013

Arthemetic Functions In Oracle



------ Upper lower Initcap -------LOWER(COLUMN/EXPRESSION)
SELECT 'oracle' String,UPPER('oracle') UPPER FROM DUAL;
SELECT ename,upper(ename),lower(ename),initcap(ename) FROM emp;
SELECT ename,job from emp where upper(job) = upper('manager');

------ Concat --------CONCAT(COLUMN1/EXPRESSION,COLUMN2/EXPRESSION)
SELECT ename,job,concat(ename,job) from emp where deptno = 10;

------ SUBSTR --------SUBSTR(COL/EXPR,M,N)
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',3,4) SUBSTRING FROM DUAL;
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',-5,4) SUBSTRING FROM DUAL;
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',0,4) SUBSTRING FROM DUAL;
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',4) SUBSTRING FROM DUAL;
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',4,0) SUBSTRING FROM DUAL;
SELECT 'ABCDEFGH' STRING,SUBSTR('ABCDEFGH',4,-2) SUBSTRING FROM DUAL;
SELECT ename,job FROM emp WHERE SUBSTR(JOB,4,3) = UPPER('age');

------ Length --------LENGTH(COL/EXPR)
Select 'oracle',length('oracle') from dual;
SELECT ename,job FROM emp WHERE LENGTH(job) = 7;

------- INSTR ------INSTR(COL/EXPR,CHAR,M,N)
SELECT ENAME,JOB,INSTR(JOB,'A',1,1) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',1,2) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',2,1) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',2,2) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',3,1) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',3,2) FROM emp WHERE job = 'MANAGER';
SELECT ENAME,JOB,INSTR(JOB,'A',2) FROM emp WHERE job = 'MANAGER';

------- LPAD -------
SELECT 'IMMENSE',LPAD('IMMENSE',15,'*') FROM DUAL;
SELECT 'IMMENSE',LPAD('IMMENSE',15) FROM DUAL;
SELECT ENAME,LPAD(ENAME,10,'-') FROM EMP WHERE SAL >= 2500;

------- RPAD -------
SELECT 'IMMENSE',RPAD('IMMENSE',15,'*') FROM DUAL;
SELECT 'IMMENSE',RPAD('IMMENSE',15),LENGTH('IMMENSE'),LENGTH(RPAD('IMMENSE',15)) FROM DUAL;
SELECT ENAME,RPAD(ENAME,10,'-') FROM EMP WHERE SAL >= 2500;
SELECT ENAME,LPAD(RPAD(ENAME,10,'-'),15,'-') FROM EMP;

------- LTRIM ------
SELECT LTRIM('IMMENSE','I') FROM DUAL;
SELECT LTRIM(JOB,'MANA') FROM EMP WHERE JOB LIKE 'MANAGER';

------- RTRIM ------
SELECT RTRIM('IMMENSE','E') FROM DUAL;
SELECT RTRIM(JOB,'ER') FROM EMP WHERE LTRIM(JOB,'MAN') LIKE 'GER';

------- TRIM -------
SELECT TRIM('S' FROM 'MITHSS') TRIMMMED FROM DUAL;
SELECT TRIM('S' FROM 'SSMITH') TRIMMMED FROM DUAL;
SELECT TRIM('S' FROM 'SSMITHSS') TRIMMMED FROM DUAL;
SELECT TRIM(TRAILING 'S' FROM 'MITHSS') TRIMMMED FROM DUAL;
SELECT TRIM(LEADING 'S' FROM 'SSMITH') TRIMMMED FROM DUAL;
SELECT TRIM(BOTH 'S' FROM 'SSMITHSS') TRIMMMED FROM DUAL;

------- REPLACE -----
SELECT REPLACE('JACK AND JUE','J','BL') FROM DUAL;
SELECT ENAME,REPLACE(JOB,'MAN','DAM') FROM EMP WHERE JOB LIKE 'MANAGER';

------- TRANSLATE ---
SELECT JOB,TRANSLATE(JOB,'P','') FROM EMP WHERE JOB='PRESIDENT';
SELECT JOB,REPLACE(JOB,'P','') FROM EMP WHERE JOB='PRESIDENT';
SELECT JOB,TRANSLATE(JOB,'MN','DM') FROM EMP WHERE JOB = 'MANAGER';
SELECT JOB,TRANSLATE(JOB,'A','O') FROM EMP WHERE JOB = 'SALESMAN';

------- CHR ----------
SELECT CHR(67)||CHR(65)||CHR(84) FROM DUAL;

------ ASCII ---------
SELECT ASCII('A'),ASCII('APPLE') FROM DUAL;


No comments:

Post a Comment

Best Blogger TipsGet Flower Effect