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