How to replace NULL with 0 in SQL Server?

There are few different ways to replace NULL with 0 in SQL Server. Let us go through them one by one.

1. Using ISNULL function

The easiest and the straightforward way to replace NULL with 0 is by using the ISNULL function.Here is an illustration of how to use it.

DECLARE @Int_Val INT;
SET @Int_Val = NULL;
SELECT ISNULL(@Int_Val, 0) AS Result;
Replace NULL with 0 in SQL Server using ISNULL

ISNULL can be used to replace NULL with any value.

2. Using COALESCE function

The next method is by using the COALESCE function. COALESCE was introduced in SQL Server 2008. We can use the COALESCE function similar to the ISNULL. However,¬†COALESCE¬†is more flexible. It can accept more than one argument and return the first non-null value from the arguments. Let’s see an example:

DECLARE @Int_Val INT;
SET @Int_Val = NULL;
SELECT COALESCE(@Int_Val, 0) AS Result;
Replace NULL with 0 in SQL Server using COALESCE

3. Using CASE statement

Using CASE is another way to replace NULL with another value. Using CASE statement is not an ideal way to replace NULL, however it’s possible. Here is an illustration:

DECLARE @Int_Val INT;
SET @Int_Val = NULL;

SELECT 
	CASE 
		WHEN @Int_Val IS NULL THEN 0 
		ELSE @Int_Val 
	END AS Result; 
Replace NULL with 0 in SQL Server using CASE

Reference


Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.