All Type Coding

Search Here

How to replace NULL value in sql server.?

Here  We will learn about different ways to replace NULL values in SQL Server.
Let's see How to replace null.Let's consider the Employees table below. 












In previous post We have learnt writing a LEFT OUTER SELF JOIN query, which produced the following output. 










In the above image output, MANAGER column, for Sandy  rows is NULL. I want to replace the NULL value, with 'No Manager' Let''s see how to replace null value using query

1- Using ISNULL() function: 
We are passing 2 parameters to IsNULL() function. If M.EmpName returns NULL, then 'No Manager' string is used as the replacement value.

SELECT E.EmpName as Employees
ISNULL(M.EmpName,'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID

The output will be:











2-Using CASE Statement:

SELECT E.EmpName as Employees
CASE WHEN M.EmpName IS NULL 
THEN 'No Manager' ELSE M.EmpName END as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID

The output will be like below:



3-Using COALESCE() function: 
COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.

SELECT E.EmpName as Employees
COALESCE(M.EmpName, 'No Manager') as Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID = M.EmpID

The output will be like above image :






1 comment :