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.
SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, ISNULL(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER
We can see how easily we have successfully changed the null value to our default value as ‘NO PASSPORT’.
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:
Using COALESCE function to solve our problem:
SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO, COALESCE(PASSPORTNUMBER,’NO PASSPORT’) as PASSPORTNUMBER FROM TBLSTUDENT
SELECT NAME, ADDRESS, COURSE, AADHAAR_CARD_NO,
PASSPORTNUMBER IS NULL THEN
END AS PASSPORTNUMBER