Different ways to replace NULL value in sql server

Sometime when we don’t know the values of the cell of the table we generally fill that as NULL. Suppose we design a table which is having information of the employee. It is having Fname, Mname, Lname, address, salary etc. may be some employee does not having middle name so in place of that we can fill as null value.

Null value is nothing but it indicates that the value is unknown. It is different from Empty and zero value. No two NULL values are equal because it returns UNKNOW value. So we can’t comparisons between two null values. So if we want to do some manipulation on that column we need the replace that null value to some value.

There is multiples way to replace the null value in sql server.

Ø  ISNULL()

Ø  CASE Statement

Ø  COALESCE()

Let’s learn step by step how to use this method to replace NULL value in sql server.

(A)   ISNULL() function

ISNULL () is built in function of the sql server. This is very used function to replace the null value. Syntax of ISNULL () function is below.

 

I am using below table

CREATE TABLE [StudentDetails](       [ID] [int] IDENTITY(1,1) NOT NULL,       [Fname] [varchar](50) NULL,       [Mname] [varchar](50) NULL,       [Lname] [varchar](50) NULL,       [SSubject] [varchar](50) NULL,       [Saddress] [varchar](100) NULL )

   

Suppose we want to get the full name subject and address from this table.

Without using Null replacement

select  Fname +'' + Mname +''+ Lname as[Full name],             SSubject,             Saddress from studentdetails

 

See here we are not getting the excepted result because we can’t perform any operation on null value.

Now I am using null replacement

select  ISNULL(Fname,'') +'' +ISNULL(Mname,'')             +''+ ISNULL(Lname,'') as[Full name],             SSubject,             Saddress from studentdetails

 

   

(B)   CASE statement

Syntax of CASE statement

    

Here if the expression return true then the result1 will be printed else the result2 will be printing.

Example

select  CASE WHEN Fname is null then '' else Fname END + '' +         CASE WHEN Mname is null then '' else Mname END + '' +         CASE WHEN Lname is null then '' else Lname END         As [Full Nme],         SSubject,         Saddress from studentdetails

 

(C)   COALESCE() function

Coalesce () function return the first non-null value. It is similar to ISNULL () function but the basic difference between them is ISNULL () function take only two arguments but Coalesce () it will take more than two argument.  Syntax of Coalesce () function is below.

 

select  COALESCE(Fname,'') +'' +COALESCE(Mname,'')         +''+ COALESCE(Lname,'') as[Full name],         SSubject,         Saddress from studentdetails