All about SQL PL/SQL and LINUX: Oracle FUCTIONS with examples 1

LIKE OPERATOR/NOT LIKE OPERATOR:

**************************************

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE'M%';

EMPNO ENAME

7654 MARTIN

7934 MILLER

SELECT EMPNO,ENAME FROM EMP WHERE ENAME NOT LIKE'M%';

EMPNO ENAME

7369 SMITH

7499 ALLEN

7521 WARD

7566 JONES

7698 BLAKE

7782 CLARK

7788 SCOTT

7839 KING

7844 TURNER

7876 ADAMS

7900 JAMES

7902 FORD

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE'%S';

EMPNO ENAME

7566 JONES

7876 ADAMS

7900 JAMES

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE'M%N';

7654 MARTIN

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE'AD%';

7876 ADAMS

SELECT EMPNO,ENAME FROM EMP WHERE ENAME LIKE'_O%';

EMPNO ENAME

7566 JONES

7902 FORD

SELECT EMPNO,ENAME,JOB FROM EMP WHERE JOB LIKE'_____';--IN THIS QUERY 5 TIME UNDERSCORE GIVEN THE RESULT WILL BE RETURN 5 CHARACTER NAMES

EMPNO ENAME JOB

7369 SMITH CLERK

7876 ADAMS CLERK

7900 JAMES CLERK

7934 MILLER CLERK

SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE LIKE'%JAN%';

ENAME HIREDATE

ADAMS 1/12/1983

MILLER 1/23/1982

SELECT ENAME,HIREDATE FROM EMP WHERE HIREDATE LIKE'%-FEB-1981'; --ERROR IS OCCURED

SELECT * FROM DEPT WHERE DNAME LIKE'_\_%'ESCAPE'\';--ERROR

IS NULL OPERATOR:IS NOT NULL OPERATOR:

********************************************

SELECT ENAME,DEPTNO,COMM FROM EMP WHERE COMM IS NULL;

ENAME DEPTNO COMM

SMITH 20

JONES 20

BLAKE 30

CLARK 10

SCOTT 20

KING 10

ADAMS 20

JAMES 30

FORD 20

MILLER 10

SELECT ENAME,DEPTNO,COMM FROM EMP WHERE COMM IS NOT NULL;

ENAME DEPTNO COMM

ALLEN 30 300

WARD 30 500

MARTIN 30 1400

TURNER 30 0

SELECT EMPNO,ENAME,JOB,MGR FROM EMP WHERE MGR IS NULL;

EMPNO ENAME JOB MGR

7839 KING PRESIDENT

SELECT EMPNO,ENAME,JOB,MGR FROM EMP WHERE MGR IS NOT NULL;

EMPNO ENAME JOB MGR

7369 SMITH CLERK 7902

7499 ALLEN SALESMAN 7698

7521 WARD SALESMAN 7698

7566 JONES MANAGER 7839

7654 MARTIN SALESMAN 7698

7698 BLAKE MANAGER 7839

7782 CLARK MANAGER 7839

7788 SCOTT ANALYST 7566

7844 TURNER SALESMAN 7698

7876 ADAMS CLERK 7788

7900 JAMES CLERK 7698

7902 FORD ANALYST 7566

7934 MILLER CLERK 7782

SELECT ENAME,DEPTNO,COMM FROM EMP WHERE COMM>=0;

ENAME DEPTNO COMM

ALLEN 30 300

WARD 30 500

MARTIN 30 1400

TURNER 30 0

ORDER BY CLAUSE:

************************************

SELECT ENAME,EMPNO,SAL FROM EMP ORDER BY SAL;

ENAME EMPNO SAL

SMITH 7369 800

JAMES 7900 950

ADAMS 7876 1100

WARD 7521 1250

MARTIN 7654 1250

MILLER 7934 1300

TURNER 7844 1500

ALLEN 7499 1600

CLARK 7782 2450

BLAKE 7698 2850

JONES 7566 2975

SCOTT 7788 3000

FORD 7902 3000

KING 7839 5000

SELECT ENAME,EMPNO,SAL FROM EMP ORDER BY SAL DESC;

ENAME EMPNO SAL

KING 7839 5000

FORD 7902 3000

SCOTT 7788 3000

JONES 7566 2975

BLAKE 7698 2850

CLARK 7782 2450

ALLEN 7499 1600

TURNER 7844 1500

MILLER 7934 1300

WARD 7521 1250

MARTIN 7654 1250

ADAMS 7876 1100

JAMES 7900 950

SMITH 7369 800

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE JOB='CLERK' ORDER BY SAL;

EMPNO ENAME JOB SAL

7369 SMITH CLERK 800

7900 JAMES CLERK 950

7876 ADAMS CLERK 1100

7934 MILLER CLERK 1300

SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE SAL>=2000 ORDER BY DEPTNO,ENAME DESC;

EMPNO ENAME JOB SAL

7839 KING PRESIDENT 5000

7782 CLARK MANAGER 2450

7788 SCOTT ANALYST 3000

7566 JONES MANAGER 2975

7902 FORD ANALYST 3000

7698 BLAKE MANAGER 2850

SELECT EMPNO,ENAME,SAL*12 "ANNSAL" FROM EMP ORDER BY ANNSAL;

EMPNO ENAME ANNSAL

7369 SMITH 9600

7900 JAMES 11400

7876 ADAMS 13200

7521 WARD 15000

7654 MARTIN 15000

7934 MILLER 15600

7844 TURNER 18000

7499 ALLEN 19200

7782 CLARK 29400

7698 BLAKE 34200

7566 JONES 35700

7788 SCOTT 36000

7902 FORD 36000

7839 KING 60000

SELECT * FROM EMP ORDER BY DEPTNO,JOB,SAL;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7934 MILLER CLERK 7782 1/23/1982 1300 10

7782 CLARK MANAGER 7839 6/9/1981 2450 10

7839 KING PRESIDENT 11/17/1981 5000 10

7788 SCOTT ANALYST 7566 12/9/1982 3000 20

7902 FORD ANALYST 7566 12/3/1981 3000 20

7369 SMITH CLERK 7902 12/17/1980 800 20

7876 ADAMS CLERK 7788 1/12/1983 1100 20

7566 JONES MANAGER 7839 4/2/1981 2975 20

7900 JAMES CLERK 7698 12/3/1981 950 30

7698 BLAKE MANAGER 7839 5/1/1981 2850 30

7654 MARTIN SALESMAN 7698 9/28/1981 1250 1400 30

7521 WARD SALESMAN 7698 2/22/1981 1250 500 30

7844 TURNER SALESMAN 7698 9/8/1981 1500 0 30

7499 ALLEN SALESMAN 7698 2/20/1981 1600 300 30

SELECT EMPNO,ENAME,JOB,SAL FROM EMP ORDER BY 2 DESC;

EMPNO ENAME JOB SAL

7521 WARD SALESMAN 1250

7844 TURNER SALESMAN 1500

7369 SMITH CLERK 800

7788 SCOTT ANALYST 3000

7934 MILLER CLERK 1300

7654 MARTIN SALESMAN 1250

7839 KING PRESIDENT 5000

7566 JONES MANAGER 2975

7900 JAMES CLERK 950

7902 FORD ANALYST 3000

7782 CLARK MANAGER 2450

7698 BLAKE MANAGER 2850

7499 ALLEN SALESMAN 1600

7876 ADAMS CLERK 1100

SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP ORDER BY 5;

EMPNO ENAME SAL JOB DEPTNO

7698 BLAKE 2850 MANAGER 30

7844 TURNER 1500 SALESMAN 30

7499 ALLEN 1600 SALESMAN 30

7654 MARTIN 1250 SALESMAN 30

7521 WARD 1250 SALESMAN 30

7900 JAMES 950 CLERK 30

7788 SCOTT 3000 ANALYST 20

7566 JONES 2975 MANAGER 20

7369 SMITH 800 CLERK 20

7876 ADAMS 1100 CLERK 20

7902 FORD 3000 ANALYST 20

7839 KING 5000 PRESIDENT 10

7934 MILLER 1300 CLERK 10

7782 CLARK 2450 MANAGER 10

THE SINGLE ROW FUNCTION CAN APPEAR IN:

*******************************************

*SELECT LIST

*WHERE LIST

*START WITH CLAUSE

*CONNECT BY CLAUSE

*ORDER BY AND HAVING CLAUSE

THE TYPES OF SINGLE ROW FUNCTIONS ARE:

---------------------------------------

*CHARACTER

*NUMBER

*DATE

*CONVERSION

*GENRAL FUNCTIONS

CHARACTER FUNCTIONS ARE TWO TYPES

1.CASE-MANIPULATION FUNCTIONS:

------------------------------

*LOWER

*UPPER

*INITCAP

2.CHARECTER-MANIPULATION FUNCTIONS:

-----------------------------------

*CONCAT

*SUBSTR

*LENGTH

*INSTR

*LPAD/RPAD

*TRIM

*REPLACE

CASE-MANIPULATION FUNCTIONS EXAMPLES:

-------------------------------------

LOWER

**********

SELECT LOWER('NARESH I TECHNOLOGIES') FROM DUAL;

naresh i technologies

SELECT ENAME,JOB,LOWER('IN OFFICE')FROM EMP;

ENAME JOB LOWER('INOFFICE')

SMITH CLERK in office

ALLEN SALESMAN in office

WARD SALESMAN in office

JONES MANAGER in office

MARTIN SALESMAN in office

BLAKE MANAGER in office

CLARK MANAGER in office

SCOTT ANALYST in office

KING PRESIDENT in office

TURNER SALESMAN in office

ADAMS CLERK in office

JAMES CLERK in office

FORD ANALYST in office

MILLER CLERK in office

SELECT ENAME,JOB,DEPTNO, LOWER(ENAME) FROM EMP WHERE DEPTNO=10;

ENAME JOB DEPTNO LOWER(ENAME)

CLARK MANAGER 10 clark

KING PRESIDENT 10 king

MILLER CLERK 10 miller

SELECT'THE'||ENAME||'"S DESIGNATION IS'||JOB FROM EMP WHERE LOWER(JOB)='manager';

'THE'||ENAME||'"SDESIGNATIONIS'||JOB

THEJONES"S DESIGNATION ISMANAGER

THEBLAKE"S DESIGNATION ISMANAGER

THECLARK"S DESIGNATION ISMANAGER

UPPER

*******************

SELECT UPPER('naresh i technologies') from dual;

NARESH I TECHNOLOGIES

SELECT ENAME,JOB,UPPER('my data') from emp;

ENAME JOB UPPER('MYDATA')

SMITH CLERK MY DATA

ALLEN SALESMAN MY DATA

WARD SALESMAN MY DATA

JONES MANAGER MY DATA

MARTIN SALESMAN MY DATA

BLAKE MANAGER MY DATA

CLARK MANAGER MY DATA

SCOTT ANALYST MY DATA

KING PRESIDENT MY DATA

TURNER SALESMAN MY DATA

ADAMS CLERK MY DATA

JAMES CLERK MY DATA

FORD ANALYST MY DATA

MILLER CLERK MY DATA

SELECT ENAME,JOB,UPPER(ename),UPPER(job) FROM EMP WHERE DEPTNO=20;

ENAME JOB UPPER(ENAME) UPPER(JOB)

SMITH CLERK SMITH CLERK

JONES MANAGER JONES MANAGER

SCOTT ANALYST SCOTT ANALYST

ADAMS CLERK ADAMS CLERK

FORD ANALYST FORD ANALYST

SELECT ENAME,JOB FROM EMP WHERE JOB=UPPER('manager');

ENAME JOB

JONES MANAGER

BLAKE MANAGER

CLARK MANAGER

SELECT UPPER('E.F CODD') "CAPITALISED" FROM DUAL;

CAPTALISED

E.F CODD

SELECT 'THE'||ENAME|| '"S DESIGNATIONS IS'||LOWER(JOB) FROM EMP WHERE JOB=UPPER('MANAGER') ORDER BY SAL;

'THE'||ENAME||'"SDESIGNATIONSIS'||LOWER(JOB)

THECLARK"S DESIGNATIONS ISmanager

THEBLAKE"S DESIGNATIONS ISmanager

THEJONES"S DESIGNATIONS ISmanager

SELECT UPPER('THE'||ENAME||'BASIC SALARY IS'||SAL)"EMP SALARIES",JOB FROM EMP WHERE JOB IN(UPPER('MANAGER'),UPPER('CLERK')) ORDER BY SAL;

EMP SALARIES JOB

THESMITHBASIC SALARY IS800 CLERK

THEJAMESBASIC SALARY IS950 CLERK

THEADAMSBASIC SALARY IS1100 CLERK

THEMILLERBASIC SALARY IS1300 CLERK

THECLARKBASIC SALARY IS2450 MANAGER

THEBLAKEBASIC SALARY IS2850 MANAGER

THEJONESBASIC SALARY IS2975 MANAGER

INITCAP FUNCTION:

**********************

SELECT INITCAP(ENAME)FROM EMP;

INITCAP(ENAME)

Smith

Allen

Ward

Jones

Martin

Blake

Clark

Scott

King

Turner

Adams

James

Ford

Miller

SELECT INITCAP('naresh i technologies') from dual;

Naresh I Technologies

SELECT'THE JOB TITLE FOR'||INITCAP(ENAME)||'IS'||LOWER(JOB) DETAILS FROM EMP;

DETAILS

THE JOB TITLE FORSmithISclerk

THE JOB TITLE FORAllenISsalesman

THE JOB TITLE FORWardISsalesman

THE JOB TITLE FORJonesISmanager

THE JOB TITLE FORMartinISsalesman

THE JOB TITLE FORBlakeISmanager

THE JOB TITLE FORClarkISmanager

THE JOB TITLE FORScottISanalyst

THE JOB TITLE FORKingISpresident

THE JOB TITLE FORTurnerISsalesman

THE JOB TITLE FORAdamsISclerk

THE JOB TITLE FORJamesISclerk

THE JOB TITLE FORFordISanalyst

THE JOB TITLE FORMillerISclerk

SELECT ENAME,UPPER(ENAME),LOWER(ENAME),INITCAP(ENAME) FROM EMP;

ENAME UPPER(ENAME) LOWER(ENAME) INITCAP(ENAME)

SMITH SMITH smith Smith

ALLEN ALLEN allen Allen

WARD WARD ward Ward

JONES JONES jones Jones

MARTIN MARTIN martin Martin

BLAKE BLAKE blake Blake

CLARK CLARK clark Clark

SCOTT SCOTT scott Scott

KING KING king King

TURNER TURNER turner Turner

ADAMS ADAMS adams Adams

JAMES JAMES james James

FORD FORD ford Ford

MILLER MILLER miller Miller

SELECT EMPNO,INITCAP(ENAME),DEPTNO FROM EMP WHERE ENAME=UPPER('ford');

EMPNO INITCAP(ENAME) DEPTNO

7902 Ford 20

CHARACTER-MANIPULATION FUNCTIONS EXAMPLES:

**********************************************

CONCAT:

*******

SELECT CONCAT('ORACLE' ,'NARESH TECHNOLOGIES')FROM DUAL;

ORACLENARESH TECHNOLOGIES

SELECT ENAME,JOB,CONCAT(ENAME,JOB)FROM EMP WHERE DEPTNO=20;

ENAME JOB CONCAT(ENAME,JOB)

SMITH CLERK SMITHCLERK

JONES MANAGER JONESMANAGER

SCOTT ANALYST SCOTTANALYST

ADAMS CLERK ADAMSCLERK

FORD ANALYST FORDANALYST

SELECT CONCAT(CONCAT(ENAME,JOB),SAL) FROM EMP;

CONCAT(CONCAT(ENAME,JOB),SAL)

SMITHCLERK800

ALLENSALESMAN1600

WARDSALESMAN1250

JONESMANAGER2975

MARTINSALESMAN1250

BLAKEMANAGER2850

CLARKMANAGER2450

SCOTTANALYST3000

KINGPRESIDENT5000

TURNERSALESMAN1500

ADAMSCLERK1100

JAMESCLERK950

FORDANALYST3000

MILLERCLERK1300

SELECT CONCAT('THE EMPLOYEE NAME IS'' ' ,INITCAP(ENAME))AS "EMPLOYEE NAMES"FROM EMP WHERE DEPTNO IN(10,20);

EMPLOYEE NAMES

THE EMPLOYEE NAME IS' Smith

THE EMPLOYEE NAME IS' Jones

THE EMPLOYEE NAME IS' Clark

THE EMPLOYEE NAME IS' Scott

THE EMPLOYEE NAME IS' King

THE EMPLOYEE NAME IS' Adams

THE EMPLOYEE NAME IS' Ford

THE EMPLOYEE NAME IS' Miller

SELECT CONCAT('&FNAME','&SNAME')"FULL NAME" FROM DUAL;

ENTER VALUE FOR FNAME:KUMAR

ENTER VALUE FOR SNAME:JABU

FULL NAME

----------

KUMARJABU

SUB STRING:

***********

SELECT SUBSTR('SIVA RAMA KRISHNA',1,4)FROM DUAL;

SIVA

SELECT SUBSTR('SIVA RAMA KRISHNA',6,4)FROM DUAL;

RAMA

SELECT SUBSTR('SIVA RAMA KRISHNA',11,17)FROM DUAL;

KRISHNA

SELECT SUBSTR('SIVA RAMA KRISHNA',11)FROM DUAL;

KRISHNA

SELECT SUBSTR('SIVA RAMA KRISHNA',-7)FROM DUAL;

KRISHNA

SELECT SUBSTR('SIVA RAMA KRISHNA',-12,4)FROM DUAL;

RAMA

SELECT SUBSTR('SIVA RAMA KRISHNA',5)FROM DUAL;

RAMA KRISHNA

SELECT SUBSTR('SIVA RAMA KRISHNA',0) FROM DUAL;

SIVA RAMA KRISHNA

SELECT SUBSTR('SIVA RAMA KRISHNA',11,5)FROM DUAL;

KRISH

SELECT ENAME,JOB FROM EMP WHERE SUBSTR(JOB,6)=UPPER('man');

ENAME JOB

ALLEN SALESMAN

WARD SALESMAN

MARTIN SALESMAN

TURNER SALESMAN

SELECT ENAME,JOB FROM EMP WHERE SUBSTR(JOB,5)=UPPER('ger');

ENAME JOB

JONES MANAGER

BLAKE MANAGER

CLARK MANAGER

SELECT CONCAT(INITCAP(ENAME),CONCAT('IS A',CONCAT(INITCAP(SUBSTR(JOB,1,3)),'EASTER.'))) FROM EMP WHERE SUBSTR(JOB,4,3)=UPPER('AGE');

CONCAT(INITCAP(ENAME),CONCAT('ISA',CONCAT(INITCAP(SUBSTR(JOB,1,3)),'EASTER.')))

JonesIS AManEASTER.

BlakeIS AManEASTER.

ClarkIS AManEASTER.

LENGTH:

*******

SELECT LENGTH('E.F CODD') FROM DUAL;

SELECT LENGTH(ENAME)||'CHARACTERS EXITS IN'||INITCAP(ENAME)||'"S NAME.'AS "NAMES AND LENGTHS "FROM EMP;

NAMES AND LENGTHS

5CHARACTERS EXITS INSmith"S NAME.

5CHARACTERS EXITS INAllen"S NAME.

4CHARACTERS EXITS INWard"S NAME.

5CHARACTERS EXITS INJones"S NAME.

6CHARACTERS EXITS INMartin"S NAME.

5CHARACTERS EXITS INBlake"S NAME.

5CHARACTERS EXITS INClark"S NAME.

5CHARACTERS EXITS INScott"S NAME.

4CHARACTERS EXITS INKing"S NAME.

6CHARACTERS EXITS INTurner"S NAME.

5CHARACTERS EXITS INAdams"S NAME.

5CHARACTERS EXITS INJames"S NAME.

4CHARACTERS EXITS INFord"S NAME.

6CHARACTERS EXITS INMiller"S NAME.

SELECT INITCAP(ENAME),JOB FROM EMP WHERE LENGTH(ENAME)=5;

INITCAP(ENAME) JOB

Smith CLERK

Allen SALESMAN

Jones MANAGER

Blake MANAGER

Clark MANAGER

Scott ANALYST

Adams CLERK

James CLERK

SELECT INITCAP(ENAME),JOB FROM EMP WHERE SUBSTR(JOB,4,LENGTH(SUBSTR(JOB,4,3)))='AGE';

INITCAP(ENAME) JOB

Jones MANAGER

Blake MANAGER

Clark MANAGER

INSTRING:

*********

SELECT INSTR('SIVA RAMA KRISHNA','A',-1,1) FROM DUAL;

SELECT INSTR('SIVA RAMA KRISHNA','A',17,2)FROM DUAL;

SELECT INSTR('SIVA RAMA KRISHNA','MA',7,1) FROM DUAL;

SELECT INSTR('SIVA RAMA KRISHNA','A',-1,1) FROM DUAL;

SELECT ENAME,JOB, INSTR(JOB,'A',1,2) FROM EMP WHERE JOB='MANAGER';

ENAME JOB INSTR(JOB,'A',1,2)

JONES MANAGER 4

BLAKE MANAGER 4

CLARK MANAGER 4

SELECT ENAME,JOB, INSTR(JOB,'A',2)FROM EMP WHERE JOB='MANAGER';

ENAME JOB INSTR(JOB,'A',2)

JONES MANAGER 2

BLAKE MANAGER 2

CLARK MANAGER 2

SELECT ENAME,JOB,INSTR(JOB,'A')FROM EMP WHERE JOB='MANAGER';

ENAME JOB INSTR(JOB,'A')

JONES MANAGER 2

BLAKE MANAGER 2

CLARK MANAGER 2

LPAD:

*****

SELECT LPAD('PAGE 1',20,'*')FROM DUAL;

**************PAGE 1

SELECT LPAD('PAGE 1',20)FROM DUAL;

PAGE 1

SELECT LPAD(ENAME,20,'@') FROM EMP WHERE DEPTNO=10;

LPAD(ENAME,20,'@')

@@@@@@@@@@@@@@@CLARK

@@@@@@@@@@@@@@@@KING

@@@@@@@@@@@@@@MILLER

SELECT LPAD('KUMAR',10,'*') FROM EMP;

LPAD('KUMAR',10,'*')

*****KUMAR

RPAD:

*******

SELECT RPAD('KUMAR',20,'*')FROM DUAL;

KUMAR***************

SELECT RPAD('KUMAR',20)FROM DUAL;

KUMAR

SELECT RPAD(ENAME,20,'@')FROM EMP WHERE DEPTNO=20;

RPAD(ENAME,20,'@')

SMITH@@@@@@@@@@@@@@@

JONES@@@@@@@@@@@@@@@

SCOTT@@@@@@@@@@@@@@@

ADAMS@@@@@@@@@@@@@@@

FORD@@@@@@@@@@@@@@@@

SELECT ENAME,LPAD(ENAME,10),RPAD(ENAME,10,'-')FROM EMP WHERE DEPTNO=10;

ENAME LPAD(ENAME,10) RPAD(ENAME,10,'-')

CLARK CLARK CLARK-----

KING KING KING------

MILLER MILLER MILLER----

SELECT ENAME,LPAD(RPAD(ENAME,10,'-'),15,'-')FROM EMP;

ENAME LPAD(ENAME,10) RPAD(ENAME,10,'-')

CLARK CLARK CLARK-----

KING KING KING------

MILLER MILLER MILLER----

LTRIM:

*******

SELECT LTRIM('XYXYORACLE 10G','XY')FROM DUAL;

ORACLE 10G

SELECT LTRIM('MM KRISHNA','M')FROM DUAL;

KRISHNA

SELECT LTRIM('JABUKUMAR','JABU')FROM DUAL;

KUMAR

SELECT LTRIM('1234DATABASE','1234')FROM DUAL;

DATABASE

SELECT LTRIM('!@#$%^&*() ORACLE','!@#$%^& ')FROM DUAL;

*() ORACLE

RTRIM:

**********

SELECT RTRIM('ORACLE 10GXYXYXY','XY')FROM DUAL;

ORACLE 10G

SELECT RTRIM('KUMAR RRRR','R')FROM DUAL;

KUMAR

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

RTRIM(JOB,'ER') JOB

MANAG MANAGER

TRIM:

************

SELECT TRIM('S'FROM 'MITHSS')FROM DUAL;

MITH

SELECT TRIM('S' FROM 'SSMITH')FROM DUAL;

MITH

SELECT TRIM('S' FROM 'SSMITHSS')FROM DUAL;

MITH

SELECT TRIM(LEADING 'S' FROM 'SSMITHSS')FROM DUAL;

MITHSS

SELECT TRIM(TRAILING 'S' FROM 'SSMITHSS') FROM DUAL;

SSMITH

SELECT TRIM(BOTH 'S' FROM 'SSMITHSS')FROM DUAL;

MITH

REPLACE:

*************

SELECT REPLACE('LED','L','R')FROM DUAL;

RED

SELECT REPLACE('LED','L','RA')FROM DUAL;

RAED

SELECT REPLACE('LED','LE','R')FROM DUAL;

SELECT ENAME,REPLACE(JOB,'MAN','DAM')FROM EMP WHERE JOB='MANAGER';

ENAME REPLACE(JOB,'MAN','DAM')

JONES DAMAGER

BLAKE DAMAGER

CLARK DAMAGER

SELECT JOB,REPLACE(JOB,'P') FROM EMP WHERE JOB='PRESIDENT';

PRESIDENT

SELECT JOB,REPLACE(JOB,'MAN','EXECLUSIVE') FROM EMP WHERE JOB='SALESMAN';

JOB REPLACE(JOB,'MAN','EXECLUSIVE')

SALESMAN SALESEXECLUSIVE

TRANSLATE:

************

SELECT TRANSLATE(JOB,'P','')FROM EMP WHERE JOB='PRESIDENT';

SELECT TRANSLATE(JOB,'MN','DM')FROM EMP WHERE JOB='MANAGER';

TRANSLATE(JOB,'MN','DM')

DAMAGER

SELECT JOB,TRANSLATE(JOB,'A','O')FROM EMP WHERE JOB='MANAGER';

JOB TRANSLATE(JOB,'A','O')

MANAGER MONOGER

SELECT JOB,TRANSLATE(JOB,'A','O') FROM EMP WHERE JOB='SALESMAN';

JOB TRANSLATE(JOB,'A','O')

SALESMAN SOLESMON

SELECT TRANSLATE('LED','LE','R')FROM DUAL;

CHR FUNCTION:

**************

SYN:CHR(N)

SELECT CHR(75)||CHR(82)||CHR(73)||CHR(83)||CHR(72)||CHR(78)||CHR(65) NAME FROM DUAL;

KRISHNA

ASCII FUNCTION:

****************

SYN:ASCII(CHAR)

SELECT ASCII('A') FROM DUAL;

SELECT ENAME,ASCII(ENAME)FROM EMP;

ENAME ASCII(ENAME)

SMITH 83

ALLEN 65

WARD 87

JONES 74

MARTIN 77

BLAKE 66

CLARK 67

SCOTT 83

KING 75

TURNER 84

ADAMS 65

JAMES 74

FORD 70

MILLER 77

SELECT ASCII('&NAME')FROM DUAL;

ASCII('KUMAR')

� / � � (%� �� pan>2450

7698 BLAKE MANAGER 2850

7499 ALLEN SALESMAN 1600

7876 ADAMS CLERK 1100

SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP ORDER BY 5;

EMPNO ENAME SAL JOB DEPTNO

7698 BLAKE 2850 MANAGER 30

7844 TURNER 1500 SALESMAN 30

7499 ALLEN 1600 SALESMAN 30

7654 MARTIN 1250 SALESMAN 30

7521 WARD 1250 SALESMAN 30

7900 JAMES 950 CLERK 30

7788 SCOTT 3000 ANALYST 20

7566 JONES 2975 MANAGER 20

7369 SMITH 800 CLERK 20

7876 ADAMS 1100 CLERK 20

7902 FORD 3000 ANALYST 20

7839 KING 5000 PRESIDENT 10

7934 MILLER 1300 CLERK 10

7782 CLARK 2450 MANAGER 10

NUMBER FUN:

*******************

THE FUN ACCEPT NUMBER INPUT AND RETURN NUMERIC VALUES.

1.ROUND

2.TRUNCATE

3.CEIL

4.FLOOR

5.MODULUS

6.POWER

7.SQUARE

8.ABSOLUTE

9.SIGN THESE ARE ALL NUMBER FUNCTIONS

ROUND:

**********

SYN:ROUND(M,N)

SELECT 19.637 NUM_,ROUND(19.647,1)ROUNDED FROM DUAL;

19.637 19.6

SELECT 19.637 NUM_,ROUND(19.657,1)ROUNDED FROM DUAL;

19.637 19.7

SELECT 7843.637 NUM1_,ROUND(7843.637,2)ROUNDED FROM DUAL;

7843.637 7843.64

SELECT 7843.637 NUM1_,ROUND(7843.637,-1)ROUNDED FROM DUAL;

7843.637 7840

SELECT 7843.637 NUM1_,ROUND(7843.637,-2)ROUNDED FROM DUAL;

7843.637 7800

SELECT 7843.637 NUM1_,ROUND(7843.637,-3)ROUNDED FROM DUAL;

7843.637 8000

SELECT 7843.637 NUM1_,ROUND(7843.637,-4)ROUNDED FROM DUAL;

7843.637 10000

SELECT 7843.637 NUM1_,ROUND(7843.637,0)ROUNDED FROM DUAL;

7843.637 7844

SELECT 7843.237 NUM1_,ROUND(7843.237,0)ROUNDED FROM DUAL;

7843.237 7843

TRUNCATE:

**********

SYS:TRUNC(M,N)

SELECT 19.637 NUM1,TRUNC(19.637,1) TRUNCATED FROM DUAL;

19.637 10

SELECT 19.637 NUM1_,TRUNC(19.637,-1) TRUNCATED FROM DUAL;

19.637 10

SELECT 19.637 NUM1_,TRUNC(19.637,-2) TRUNCATED FROM DUAL;

19.637 0

SELECT 19.637 NUM1_,TRUNC(19.637,-3) TRUNCATED FROM DUAL;

19.637 0

SELECT 19.637 NUM1_,TRUNC(19.637,-4) TRUNCATED FROM DUAL;

19.637 0

SELECT 19.637 NUM1_,TRUNC(19.637,0) TRUNCATED FROM DUAL;

19.637 19

SELECT 19.637 NUM1_,TRUNC(19.637) TRUNCATED FROM DUAL;

19.637 19

CEIL FUN:

****************

SYN:CEIL(N)

SELECT 19.001 NUM1,CEIL(19.001)CEILED FROM DUAL;

19.001 20

SELECT 19.34 NUM1,CEIL(19.32)CEILED FROM DUAL;

19.34 20

SELECT 19.34 NUM1,CEIL(19.2)CEILED FROM DUAL;

19.34 20

SELECT 19.34 NUM1,CEIL(19)CEILED FROM DUAL;

19.34 19

SELECT 19.34 NUM1,CEIL(19.0)CEILED FROM DUAL;

19.34 19

FLOOR FUN:

*************

SYN:FLOOR(N)

SELECT 19.001 NUM1,FLOOR(19.999) FLOOR FROM DUAL;

19.001 19

SELECT 18.34 NUM1,FLOOR(18.34)FLOOR FROM DUAL;

18.34 18

SELECT 18.34 NUM1,FLOOR(18.9)FLOOR FROM DUAL;

18.34 18

SELECT 18.34 NUM1,FLOOR(18)FLOOR FROM DUAL;

18.34 18

MODULUS FUN:

*************

SYN:MOD(M,N)

SELECT MOD(100,10)MODULUS FROM DUAL;

SELECT MOD(45,7)MODULUS FROM DUAL;

SELECT MOD(17,4)MODULUS FROM DUAL;

POWER FUN:

***********

SYN:POWER(M,N)

SELECT POWER(5,2)POWER FROM DUAL;

SELECT POWER(5,3)POWER FROM DUAL;

125

SELECT POWER(-5,2)POWER FROM DUAL;

SELECT POWER(5,-2)POWER FROM DUAL;

0.04

SELECT POWER(-5,-2)POWER FROM DUAL;

0.04

SQUARE FUN:

**************

SYN:SQRT(N)

SELECT SQRT(25) FROM DUAL;

SELECT SQRT(7) FROM DUAL;

2.64575131106459

SELECT SQRT(64) FROM DUAL;

ABSOLUTE FUN:

***************

SYN:ABS(N)

SELECT ABS(-100)FROM DUAL;

100

SELECT SAL,COMM,SAL-COMM,ABS(SAL-COMM) FROM EMP;

SAL COMM SAL-COMM ABS(SAL-COMM)

800

1600 300 1300 1300

1250 500 750 750

2975

1250 1400 -150 150

2850

2450

3000

5000

1500 0 1500 1500

1100

950

3000

1300

All about SQL PL/SQL and LINUX

Tuesday, 7 August 2012

Oracle FUCTIONS with examples 1

LIKE OPERATOR/NOT LIKE OPERATOR:

**************************************

IS NULL OPERATOR:IS NOT NULL OPERATOR:

********************************************

ORDER BY CLAUSE:

************************************

THE SINGLE ROW FUNCTION CAN APPEAR IN:

*******************************************

THE TYPES OF SINGLE ROW FUNCTIONS ARE:

---------------------------------------

1.CASE-MANIPULATION FUNCTIONS:

------------------------------

2.CHARECTER-MANIPULATION FUNCTIONS:

-----------------------------------

CASE-MANIPULATION FUNCTIONS EXAMPLES:

-------------------------------------

LOWER

**********

UPPER

*******************

No comments:

Post a Comment

Expose like a sun.