General Function in SQL

Posted on by

General Functions are :

  1. NVL function (i.e NULL VALUE function )
  2. NVL2 function
  3. Coalesce function

NVL Function:

  • NVL functions converts a NULL value to an actual value.
  • Data types that can be used are date, char and number
  • Datatype must match for a NVL function.
  • NVL ( arg1 , arg2 );

Example : 

Question : display the null values are replaced by salary values in commission from employee table.

Query : SELECT ename, comm, sal, NVL( comm, sal) as actualvalue from emp;

NVL2 Function :

  • It holds 3 arguments
  • NVL2 replace the actual value to some other value.

  • 2nd argument – actual
  • 3rd argument – actual value

Example :

Question: Display employee salary who are not having commission instead 100 is replace.

Query : SELECT ename, sal, comm, 100, NVL2( sal, comm, 100) FROM emp;

COALESCE Function :

  • The advantage of the coalesce function over the NVL function is that the coalesce function can take multiple alternate values.
  • If the first expression is not null, the coalesce function returns that expression, otherwise it does the coalesce of the remaining expression.

Difference between NVL and NVL2:

  • In NVL the first argument is taken as acutal value.
  • In NVL2 the second and third argument is taken as actual value.