CHARECTER MANIPULATION FUNCTION
Lenth-it will return number of charecters of given string
ex select length(ename),ename from emp;
Q) display name of employer having name exactly for charecters
REVERSE-
it is used to reverse the charecters of given string
example---
select reverse('abc') from dual;
select reverse('123') from dual;
select reverse(123) from dual;
REPLACE-
IT USED to replace the charecters of given string
syntax replace ('string','char to be replace','char to b ereplace by')
3rd parametr is optional by defualt it act as removed
examples---
replace('qspider','r','d') from dual;
replace ('qspider','s') from dual;
{NESTED REPLACE FUNCTION--
1.SELECT REPLACE(REPLACE('QSPIDER','I','!'),'E','@') FROM DUAL;}
2.SELECT REPLACE(REPLACE(ENAME,'S','$'),'T','#'),ENAME FROM EMP WHERE ENAME IN
('SMITH','SCOTT');
3.SELECT LENGTH('HELLO')-LENGTH(REPLACE('HELLO','L')) FROM DUAL;
SUBSTRING--------------
SUBSTRING IS USED TO FETCH SUBSTRING OUT OF THE MAIN STRING
Syntax - SELECT (STRING,POSITION,LENGTH) FROM DUAL;
LAST PARAMETER IS OPTIONAL BY DEFUALT IT WILL DISPLAY STRING TILL END
negative number in start position seraching will start from reverse direction
1) substr('onkar',4,4) from dual;
2) substr ('onkar',-1,3) from dual;
3)substr('onkar',-1,2),substr('onkar',-1,1),substr('onkar',-1) from dual;
4) substr('onkar',0,2) from dual;
DISPLAY NAME OS EMPLOYEE STARTV WITH A OR SCOTT
SELECT ENAME FROM EMP WHERE ENAME LIKE 'A%' OR ENAME LIKE 'S%';
select substr (ename,1,1) in ('a','s');
TRIM------------
TRIM IS A USED TO TRIM THE CHARECTER FRM GIVEN STRING
SYNTAX-select trim('char' from 'string') from dual;
1)SELECT TRIM('R' FROM 'ONKAR') FROM DUAL;
2)SELECT TRIM ('O' FROM 'ONKAR') FROM DUAL;
SELECT TRIM (BOTH 'N' FROM 'NITIN') FROM DUAL;
SELECT TRIM ('N'FROM'NITIN') FROM DUAL;.......ITI ANSWER;
SELECT TRIM ('ON' FROM 'ONKAR') FRROM DUAL; ...WE WILL GET ERROR BCZ WE USED TWO
CARECTERS IN CONDITION...;
genral function(NULL VALUE)---
1)SYNTAX-
NVL FUNCTION IS USED TO SUBSTITUTE AN ALTERNATE VALUE FOR NULL
NVL WILL TAKE TWO ARGUMENTS IF VALUE OF 1 ARGUMENT IS NULL THEN FUNCTION WILL
RETURN VALUE OF SECOND ARGUMENT
IF VALUE OF 1 ARGUMENT IS NOT NULL THEN FUNCTION WILL RETURN VALUE OF 1 ARGUMENT
ONLY
WRT A QUERY TO DISPLAY TOTAL SALARY SALARY+COMM OF EMP;
SELECT ENAME,SAL,SAL+NVL(COMM,0)"TOTAL SAL" FROM EMP;
ENAME SAL TOTAL SAL
---------- ---------- ----------
SMITH 800 800
ALLEN 1600 1900
SELECT ENAME,SAL,SAL+NVL(COMM+100,100)"TOTAL SAL" FROM EMP;
ENAME SAL TOTAL SAL
---------- ---------- ----------
SMITH 800 900
ALLEN 1600 2000
WARD 1250 1850
JONES 2975 3075
MARTIN 1250 2750
NVL2----
NVL2 IS ENHANCEMENT OVER NVL FUNCTION
IF VALUE OF 1 ARGUMENT IS NULL THEN FUNCTION WILL RETUN VALUE OF THRID ARGUMENT
IF VALUE OF FIRST ARGUMENT IS NOT NULL THEN FUNCTION WILL RETUN VALUE OF SECOND
ARGUMENT
Syntax--nvl2(arg1,arg2,arg3);
nvl2 functionwill return an alternate value for null an also for not null
wqtd total salary (salary+100) of all emply,increment comm of all emp by RS 100...
SOLUTION--
SELECT ENAME,COMM,SAL,SAL+NVL2(COMM,COMM+100,100) "TOTAL SAL" FROM EMP;
ENAME COMM SAL TOTAL SAL
---------- ---------- ---------- ----------
SMITH 800 900
ALLEN 300 1600 2000
WARD 500 1250 1850
JONES 2975 3075
MARTIN 1400 1250 2750
BLAKE 2850 2950
CLARK 2450 2550
DECODE FUNCTION WILL WORK LIKE IF ELSE STATEMENT
Syntyax--SELECT DECODE(COLUMN NAME,SEARCHING,RESULT)
DECODE FUNCTION COMPAIRES SEARCH VALUE AGAINST COLUMN VALUES
IF EQUALLITY EXISTS THEN FUNCTION WILL RETURN ITS RESPECTIVE RESULT
IF EQUALLITY DESNT EXISTS THEN FUNCTION WILL RETURN DEFAULT VALUE (IF PROVIDED)
OTHERWISE FUNCTION WILL RETURN NULL
WQTD INCREMENTED SALARY OF ALL MANAGERS BY 100 AND ANALYST BY 50 ?
SELECT ENAME,SAL,JOB,DECODE(JOB,"MANAGER",SAL+100,"ANALYST",SAL+100,SAL)"INCR SAL"
FROM EMP;
COALESCE FUNCTION
IT IS SIMILER TO NVL I.E IT WILL RETURN ALTERNATE VALUE FOR NULL
COALESCE CAN TAKE MULTIPLE ARGUMENTS BUT TO THE NVL FUNCTION WE CAN PROVIDE ONLY
TWO AERGUMENTS
SELECT COALESCE(NULL,NULL,'A','B') FROM DUAL;
SELECT COALESCE('ABC',NULL,'A','B') FROM DUAL;
NULL IF --
IT COMPARES TWO VALUES IF EQUALITY EXISTS THEN FUNCTION WILL RETURN NULL
SELECT NULLIF('SUNDAY','SUNDAY') FROM DUAL;----NULL
SELECT NULLIF('SUNDAY','MONDAY') FROM DUAL;----SUNDAY
IF EQUALITY DOESNT EXISTS THEN FUNCTION WILL RETUN 1 ARGUMENT
