Replace Nulls With Specified Values in SQL Server

To replace null with specified replacement value, we can use any of the following:
  1. ISNULL() function
  2. CASE statement
  3. COALESCE() function
For the demo, we will be using this tblPerson table.
 
table 
 
In the table we have three columns P_Id, Name, and Gender. P_Id is the primary column of this table.
 
The following is the query for creating this table.
  1. CREATE DATABASE db_ReplaceNull;  
  2.   
  3. USE db_ReplaceNull;  
  4.   
  5. CREATE TABLE tblPerson(  
  6.    P_Id INT CONSTRAINT pk_Id PRIMARY KEY,  
  7.    Name NVARCHAR(50),  
  8.    Gender NVARCHAR(10)  
  9. );  
  10.   
  11. INSERT INTO tblPerson VALUES (101,'Sam','Male'),(102,'Sara','Female'),(103,'Michael',null),  
  12. (104,null,null),(105,null,'Female'),(106,'Max',null),(107,'Aiden Pearce','Male');  
When I execute this query.
  1. SELECT P_Id,Name,Gender FROM tblPerson;  
I get the following records.
 
records 
 
The preceding query returns many columns with null values. Now let's say there is a requirement to replace all these null values with meaningful text. For example, replace null with "no name" for the name column and replace null with "no gender" for the gender column.
 

ISNULL Function in SQL Server

 
The ISNULL Function is a built-in function to replace nulls with specified replacement values.
 
To use this function, all you need to do is pass the column name in the first parameter and in the second parameter pass the value with which you want to replace the null value.
 
ISNULL Function in SQL Server 
ISNULL Function in SQL Server 
 
So, now all the null values are replaced with No Name in the Name column.
 
Now let's do the same for the Gender column.
 
Pass the column Gender in the parameter of the ISNULL function and in the second parameter pass the replacement value.
 
ISNULL Function in SQL Server 
 

CASE statement in SQL Server

 
To begin a case statement we use:
 
CASE
 
If the column value is null then that value will be replaced with the "replacementValue".
 
WHEN ColunName IS NULL THEN 'replacementValue'
ELSE
 
If the column value is not null then it will print the following column value:
 
ColumnName
 
To end the case, use "end".
 
END
 

COALESCE() function in SQL Server

 
The COALESCE() function returns the first NON NULL value.
 
If you look at this table.
 
COALESCE function 
 
There are two nulls in the Name column and three nulls in the Gender column and with the help of COALESCE we will get the first non-null value from both of the columns.
 

How to use COALESCE function

 
Pass the column names separated by commas as a parameter.
  1. SELECT P_Id, COALESCE(Name,Gender) AS [First Non-Null Value] FROM tblPerson;  
When we execute the query, we will get Sam, Sara, Michael and Null (because both the rows are null), Female, Aiden Pearce.
 
COALESCE function 
 
So, in this article we saw how to replace null values in a column.
 
I hope you like it. Thank you.