how to replace null with 0 in sql server

 3,801 Total Views,  1 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.


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.

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
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.

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

  1. Excellent explanation, Thank you so much. Today I have know three types ways to replace NULL in sql server.

  2. Update table [AdventureWorks2012]
    Set column [CreditCardApprovalCode]=o
    Where column [CreditCardApprovalCode] is null

    1. Hi,
      Mr. Mizan Thank you for your query.
      You can use below code to update CreditCardApprovalCode to zero(0)
      where CreditCardApprovalCode is null

      use [AdventureWorks2012]

      Update sales.SalesOrderHeader
      Set [CreditCardApprovalCode]=0
      Where [CreditCardApprovalCode] is null;

      Thanks again.

  3. 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 *