DECODE
Decode function has the functionality of an IF-THEN-ELSE statement. Decode does a value-by-value substitution. For every value that is given in the DECODE function it makes if then check and matches the value.
Syntax-Decode (Value, if-search-1,result1,if-search-2,result2,if-search-3,result3,if-search-1,default-result)
Example
select empname,Decode(empid,1001,20000,1002,12000,1003,15000,null) from emp;
This query is selecting the emp name and salary on basis of emp id. if emp id is 1001 then salary is 20000
and if emp id is 1002 then salart is 12000 and so on.
NVL
NVL function can substitute a value when null value is there.For ex-
select NVL(empname,vinay) from emp.
This query will select empname from table and if empname is null ,it will return you ‘VINAY’.
SUBSTR
This function is extract a part of string from string.
Syntax for function is – Substr(String,starting_point,end_point)
Example-
Substr(‘Vinay is best’,5,3) – result (y i)
Substr(‘Vinay is best’,4,7) – result (ay is b)
Substr(‘Vinay is best’,7) – result (is best)
Substr(‘Vinay is best’,-7,5) – result (is be) when negative value is there,Starting point will start frm . end and then move backward
Substr(‘Vinay is best’,-4,3) – result (bes)
WildCard Search using Bind variable :-
Now i am showing the wild card functionality in select query using the bind variable.
Select empname and empsalary from emp where UPPER(nvl(employeeid,”)) like
decode(:empid,”,’%’,upper(replace(:empid,’*’,’%’)))
This query is working on wild card search functionality. you can search data by using ‘*’ instead of ‘%’.I am using NVL,UPPER and DECODE function together to give you better understanding. This query will run as wild card search query. If user don’t give any empid, it will have a ” value through NVL function. In decode when empid is ” then it will return as ‘%’ that mean all record. If empid is not ” then value in bind variable surrounding by ‘%’ and replace function is replacing the ‘*’ to ‘%’.In this way, it is working like wild card search.If user enter *32* then any empid having 32 anywhere will returned.
NULLIF
This function will compare the expression. And if they are equal then it will return null. Otherwise it will
Return first value .
for example–
NULLIF(5,5) result would be — NULL
NULLIF(3,4) result would be — 3
NULLIF(6,3) result would be — 6