Wednesday, 9 December 2015

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
 


No comments:

Post a Comment

If you have any doubt, please let me know.

Popular Posts