Friday, 17 January 2014

What are the different ways to replace NULL values in SQL Server

This interview question is not that common. My friend faced this interview question, when he attended an interview in London. My friend said we can use  COALESCE() in SQL Server. Then the interviewer said, that's very good answer, but do you know of any other way? 

Apart from using COALESCE(), there are 2 other ways to replace NULL values in SQL Server. Let's understand this with an example.

I have a Table tblEmployee, as shown in the diagram below. Some of the Employees does not have gender. All those employees who does not have Gender, must have a replacement value of 'No Gender' in your query result. Let's explore all the 3 possible options we have.

Option 1 : Replace NULL values in SQL Server using ISNULL() function.

Select Name, ISNULL(Gender,'No Gender') as Gender
From tblEmployee

Option 2 : Replace NULL values in SQL Server using CASE.

Select Name, Case  When Gender IS NULL Then 'No GenderElse Gender End as Gender
From tblEmployee

Option 3 : Replace NULL values in SQL Server using COALESCE() function.

Select Name, Coalesce(Gender, 'No Gender') as Gender
From tblEmployee

These are the 3 options that I can think of at the moment. If you can think of any other option, please post it here.

No comments:

Post a Comment

Paging in html table using javascript

I demonstrated how to use HTML tables on the client for a very simple client-side paging solution. I have heard from several people who p...