SQL functions
About author I am Java/oracle professional.Working on Java/J2EE technologies and i.e Oracle ADF,Spring,hibernate,J2ee,PL/sql,Apps for 2+ years.I am passionate about learning new technologies.I am sharing my knowledge. Give your views and suggestion.
http://www.linkedin.com/in/vinaykumar2 Read more from this author
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
Comments
Good work…. vinay is a genius
It would be interesting to specify the impact on performance: is DECODE better than IF… THEN…ELSE…END IF ?
I am writing some more sql code for information advice by my friend for wild card search using case function.
SQL> select * from emp
2 where ename = (case when ‘&&emp_name’ = ‘%’ then ename
3 else ‘&&emp_name’ end)
4 /
Enter value for emp_name: CAVE
old 2: where ename = (case when ‘&&emp_name’ = ‘%’ then ename
new 2: where ename = (case when ‘CAVE’ = ‘%’ then ename
old 3: else ‘&&emp_name’ end)
new 3: else ‘CAVE’ end)
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7844 CAVE SALESMAN 7698 08-SEP-81 1500 0
30
SQL>
SQL> undef emp_name
SQL> select * from emp
2 where ename = (case when ‘&&emp_name’ = ‘%’ then ename
3 else ‘&&emp_name’ end)
4 /
Enter value for emp_name: %
old 2: where ename = (case when ‘&&emp_name’ = ‘%’ then ename
new 2: where ename = (case when ‘%’ = ‘%’ then ename
old 3: else ‘&&emp_name’ end)
new 3: else ‘%’ end)
EMPNO ENAME JOB MGR HIREDATE SAL COMM
———- ———- ——— ———- ——— ———- ———-
DEPTNO
———-
7369 CLARKE CLERK 7902 17-DEC-80 800
20
7499 VAN WIJK SALESMAN 7698 20-FEB-81 1600 300
30
7521 PADFIELD SALESMAN 7698 22-FEB-81 1250 500
30
…
Hey want to add one more point.
If you compare performance wise Decode and case.then there is not much differnces between them.Some time case is better and sometime Decode is better.It depend on CPU architecture .Case is easy to write and it have more readbility.
and if you compare IF ELSE with DECODE ,according to me Decode is much better than IF ElSE.
Trackbacks