1. What can we do with Special Functions ?
We can do the following:
(i) Replace NULL values.
(ii) Use If-else condition.
(iii) Find the Greatest and Least.
(iv) Change ASCII value to Character value and viceversa.
(i) Replace NULL values.
(I) NVL:
If expr1 is not null, it will return expr2.If expr1 is null, return expr2.
Syntax:
=> NVL( expr1, expr2 )
Examples:
=> NVL( 101, 102 ) = 101
=> NVL( null, 100 ) = 100
=> NVL( null, null ) = null
=> Select Sal + NVL( comm, 0 ) as "Total Salary" from Emp;
= 100
(II) COALESCE:
Returns the first non null value.Syntax:
=> COALESCE( expr1, expr2, expr3, ....)
Examples:
=> COALESCE( null, 100, 200 ) = 100
=> COALESCE( 100, 200 ) = 100
=> COALESCE( 123, 200, null ) = 123
(ii) Use If-else condition.
(I) NVL2:
If expr1 is not null, it returns expr2.If expr1 is null, it returns expr3.
Syntax:
=> NVL2( expr1, expr2, expr3 );
Examples:
=> NVL2( 10,20,30 ) = 20.
=> NVL2( null,20,30 ) = 30.
=> Select NVL2( comm, comm, 0 ) from emp;
(II) DECODE:
Works same as If-then-else.Syntax:
=> DECODE( expr1, value1, return expr1,
value2, return expr2,
value3, return expr3, .....
, return Default expr)
If default expr is not provided, it returns NULL.
Examples:
=> DECODE( 3+2, 5, return 5, 6, return 6) = 5
=> Select DECODE( JOB, 'CLERK', 'WORKER',
'MANAGER', 'BOSS',
'PRESIDENT', 'BIG BOSS',
,' EMPLOYEE')
From EMP;
(iii) Find the Greatest and Least.
(I) GREATEST()
Returns greatest value among the given value.Syntax:
=> GREATEST ( expr1, expr2, expr3, ....)
Examples:
=> GREATEST ( 10,20,30 ) = 30
=> GREATEST ( 10,30,12 ) = 30
=> GREATEST ( 30,45,5 ) = 45
(II) LEAST()
Returns least value among the given values.Syntax:
=> LEAST ( expr1, expr2, expr3, ....)
Examples:
=> LEAST ( 10,20,30 ) = 10
=> LEAST ( 10,30,12 ) = 10
=> LEAST ( 30,45,5 ) = 5
(iv) Change ASCII value to Character value and viceversa.
(I) CHR()
Converts the ASCII value to the respective character.Syntax:
=> CHR( ASCII_value )
Examples:
=> CHR(65) = A
=> CHR(97) = a
(II) ASCII()
Converts the CHAR value to the respective ASCII value.Syntax:
=> ASCII( character )
Examples:
=> ASCII( 'A' ) = 65
=> ASCII( 'a' ) = 97