ISNULL , NVL , NULLIF , NVL2 , Coalesce function for handling null value

ISNULL, NVL, NVL2, NULLIF, Coalesce function for handling null value

Null value is unknown, undefined value.

SQL Server :

ISNULL ( column/expression , replace_value )

Isnull first expression find the null value
then it will replace it the secondary value defined.

SELECT ISNULL(Salery ,0) FROM HR.Employee;

NULLIF (exp1 , exp2 )

NULLIF returns the first expression if the two expressions are not equal.
Otherwise NULLIF returns a null value.

#select nullif(column_value1, column_value2) from employee;

ORACLE:

NVL (column/expression, Replace_value)

NVL first expression find the null value then
it will replace it the secondary value defined.

Select NVL(Salary, 0 ) from HR.employee;

NULLIF (exp1 , exp2 )

NULLIF returns the first expression if the two expressions are not equal.
Otherwise NULLIF returns a null value.

#select nullif(column_value1, column_value2) from employee;

NVL2 (column/exp, Value if not null, value if null);

# select NVL2( city, ‘present’, ‘n/a’) from employee;

COALESCE(value1, value2, value3)

Function returns the value of the first of its input parameters that is not NULL.

# select coalesce (address1 , address2 ) from employees;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.