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 3rd 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
I am Rajat Jaiswal from India. I am working as a Technology specialist in one of the reputed company in India with 12+ years of experience. I am a Microsoft Data Platform MVP & Alibaba Cloud MVP.
I have always had an affinity to learn something new in technology. Database, Web development, Cloud computing are the areas of my interests always.
I have been a regular contributor to technologies with more than 300+ tips. Learning & Sharing is one of my aims always. Writing blogs, delivering sessions, helping on forums is my hobby.