Handling Null values with different in build functions in SQL SERVER

 

Sometimes there are scenarios where we have some column values as null, i.e. when we will retrieve those value from the database they will return as null. So in this scenario, if the column value has null we want null to be replaced by some other default value which can be achieved by using different functions in SQL server. This blog is basically focused on solving null values using in build function in SQL server.

So we have a table called TBLSTUDENT
which has following records:

 

 

As we can see above there are some students which don’t have their Passport. So now if we will be showing them in the UI or Export data they will be shown as Empty, But we want to replace them with NoPassport rather than showing as null. So in order to achieve the same, we can make use of IsNull(), Case & Coalesce() function.

 

  • ISNULL

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, ISNULL(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER
FROM TBLSTUDENT

 

Output:

 


We can see how easily we have successfully changed the null value to our default value as ‘NO PASSPORT’.

 

 

  • COALESCE

Coalesce function is used to return the first non-null value from the passed expression. We can pass multiple expression to it as shown below:

Example:

SELECT COALESCE(null,null,null,’sasd’)

 

Using COALESCE function to solve our problem:

 

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, COALESCE(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER FROM TBLSTUDENT

 

Output:

 

 

  • CASE

 

SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO,

CASE WHEN

PASSPORTNUMBER IS NULL THEN

‘NO PASSPORT’

ELSE

PASSPORTNUMBER

END AS PASSPORTNUMBER

FROM TBLSTUDENT

 

Output:


Happy Learning..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.