You would have seen in my previous blog that how Nulls can be tested. If you want to refer that again, then browse this link:

In this blog post, I will deal with how to handle the Nulls using some functions.

Handling NULLS:

At the time, when we supply the data to the end users, or to any applications so the Null should either be removed or should be replaced with a valid value so that the data may be understood, and the expression wont fail.

Since Nulls require special handling when used within expressions, so SQL includes three functions, COALESCE () and ISNULL () for converting Nulls to usable values, and NULLIF () for creating a Null if the condition is being met. We will discuss each one of them briefly:

COALESCE () function:

  • COALESCE () function accepts a list of expressions or columns and returns the first non-null value.
  • It can be represented as: COALESCE (expression1, expression2 …).
  • It generally means to unite towards a common end, to grow together, and to bring opposite sides together for a common good.
  • This function brings together multiple, differing values of unknown usefulness, and from them emerges a single valid value.
  • It is not used as often as it should be, might because of not well-known.
  • For example;
    • The following code demonstrates the COALESCE () function returning the first non-null value-

The results can be displayed as follows:

1_Working_with_NULLS_in_SQL_Server_PART_2

  • It will become more clear if you carefully refer to this example, in which the Students belong to which stream is being displayed:

1. First , we have to create a table-

2. Then, insert some records-

3. Let see the table now-

2_Working_with_NULLS_in_SQL_Server_PART_2

As you can see here, there are many NULL present inside the table. So what we have to do is to handle the NULL values, i.e., to replace them with some strings. We can do this by using a COALESCE() function.

4. So by using COALESCE() function we will replace the NULLs with string ‘NO’, which may be understood by the end-users:

The table can be viewed in a result set as follows:

3_Working_with_NULLS_in_SQL_Server_PART_2

Well this was all about COALESCE() function for this post, and in the next blog post I would like to deal with two more functions ISNULL() and NULLIF().

So keep in touch!

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook