Find First Not Null Value From Different Columns: TIP# 47

Problem

Sometimes, it might be possible that we need not null value only from particular columns and if all columns have null value, then we provide a default value.

Let's understand this by a general and very interesting example.

Suppose a friend comes to your house and you want to give him a treat, then you check your first column or we can say first option “Is there anything to eat?” if that value is null then you go for second column or we can say second option “Is there anything to drink?”

If that value is also null, then you will choose 3^rd or default option and ask friend to give you treat.

Isn’t it simple ? Just kidding .

Let’s understand now with adventurework’s product table.

Suppose we want to fetch productId, productname, productnumber, and any property (either color, class) and if both the columns (color, class) are null, then we need to display “No Property found” in the column value.

So, I wrote the following query to achieve this:

SELECT PRODUCTID ,
      Name,
     ProductNumber,
COALESCE(Color,class,’No Property found’) As productProperty
FROM [Production].[Product]

So if you observe the above figure, you will find in the records where color found color value appear and if color value is null and class value found, the class value appears and if both color and class value are null, then we provide simple value which is “No Property found”.

I hope this may help you.

Thanks & enjoy!

Filed under: CodeProject, DENALI, Sql server, SQL SERVER 2008 - R2, SQL SERVER 2014, TIPS
Tagged: COLEASCE, ISNULL, NULL