In legacy data, it is very common that you find a lot of unnecessary NULL values and you need to do massage to present this data, whether it is a report or an email. Generally, we use few techniques to avoid NULL and replace it with any characters or numbers.
Before proceeding with the solution, I would like to create a sample to demonstrate the solution.
SAMPLE :
DECLARE @tbl_sample TABLE ( [ID] INT, [Name] VARCHAR(50), [Code] INT ) INSERT INTO @tbl_sample VALUES(1,'Bob',1234) INSERT INTO @tbl_sample VALUES(2,'Sandra',NULL) INSERT INTO @tbl_sample VALUES(3,'Mike',NULL) SELECT * FROM @tbl_sample --OUTPUT
METHOD 1 :
Given below is the script to replace NULL using ISNULL (a SQL Server built-in function).
SELECT [ID] ,[Name] ,ISNULL([Code],0) AS [Code] FROM @tbl_sample --OUTPUT
METHOD 2 :
Given below is the script to replace NULL using COALESCE (a SQL Server built-in function).
SELECT [ID] ,[Name] ,COALESCE([Code],0) AS [Code] FROM @tbl_sample --OUTPUT
METHOD 3 :
Given below is the script to replace NULL using CASE STATEMENT (a SQL Server expression).
SELECT [ID] ,[Name] ,CASE WHEN [Code] is NULL THEN 0 ELSE [Code] END As [Code] FROM @tbl_sample --OUTPUT
SQL Server Portal 