Working with NULLS in SQL Server – PART 2

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-

SELECT COALESCE(NULL, 100+NULL, 7, 'A');

The results can be displayed as follows:

• 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-

USE TEST
CREATE TABLE Student
(
S_Id int not null,
Name varchar(50) not null,
Enrl_No nvarchar(50) not null,
Science_Stream varchar(10) null,
Commerce_Stream varchar(10) null,
Art_Stream varchar(10) null

2. Then, insert some records-

INSERT INTO Student (S_Id, Name, Enrl_No, Science_Stream, Commerce_Stream, Art_Stream)
SELECT 1, 'Piyush Bajaj', 'AC2389', 'Yes' ,NULL, NULL
UNION
SELECT 2, 'Prashant Tiwari', 'ADY654', NULL, 'Yes', NULL
UNION
SELECT 3, 'Ankit Ritolia', 'AS2378', NULL, NULL, 'Yes'

3. Let see the table now-

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:

SELECT S_Id, Name, Enrl_No, 
COALESCE(Science_Stream,'NO') AS Science_Stream, 
COALESCE(Commerce_Stream,'NO') AS Commerce_Stream,
COALESCE(Art_Stream,'NO') AS Art_Stream
FROM Student;

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

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