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;