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.