how to replace null with 0 in sql server

 916 Total Views,  4 views today

Problem: Replace null value with 0 in sql server
You are selecting rows from a table, and your results contain NULL values. You would like to replace the NULL
values with zero (0) or an alternate value.

Syntax:

ISNULL ( check_expression , replacement_value )
Example: SELECT ISNULL(myColumn, 0 ) FROM myTable

It is important to note that the return type of ISNULL function is the same as the data type of the first parameter.

Solution:1
Select id,Name,Phone, ISNULL(salary,0) as [Salary] from Employee
Solution: 2
Select id,Name,Phone, case when salary IS NULL then 0 else salary end as [Salary] from Employee
Solution: 3
Select id,Name,Phone, COALESCE(salary,0) as [Salary] from Employee
Solution: 4
USE AdventureWorks2012
Go
SELECT h.SalesOrderID,h.CreditCardApprovalCode,
ISNULL(h.CreditCardApprovalCode,0) AS CreditApprovalCode_Display
FROM Sales.SalesOrderHeader h ;
how to replace null with 0 in sql server
Figure: how to replace null with 0 in sql server

In above example, the column CreditCardApprovalCode contains NULL values for rows where there is no credit
approval. This query returns the original value of CreditCardApprovalCode in the second column. In the third
column, the query uses the ISNULL function to evaluate each CreditCardApprovalCode. If the value is NULL, the
value passed to the second parameter of ISNULL zero (0) is returned.

2 thoughts on “how to replace null with 0 in sql server”

  1. Thanks for your good solutions. I want to desperately know “What is the difference between ISNULL and COALESCE in SQL”. Please help me ….

Leave a Reply

Your email address will not be published. Required fields are marked *