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