2,783 Total Views, 12 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.
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 ;
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.