How to Replace Null values with "Unknown" in Select Statement in SQL Server - SQL Server / TSQL Tutorial Part 111


You are working as SQL Server developer, you need to query dbo.Customer table and replace Null values in Name column to  "Unknown Name". Which SQL functions will you use to replace Null value with "Unknown Name"?


You can use ISNULL or COALESCE functions to replace Null with value we like. Let's create dbo.Customer table and insert sample records by using below statements.

--Create dbo.Customer table
Create table dbo.Customer(
Id int,
Name VARCHAR(10),
Address VARCHAR(100))

--Insert records in dbo.Customer table
Insert into dbo.Customer
Values(1,'Aamir','XyZ Address')
,(3,null,'abc address')

Let's use ISNULL or COALESCE functions to replace Null with "Unknown Name".

Select Id,ISNULL(Name,'Unknown Name') AS NameByISNull,
COALESCE(Name,'Unknown Name') NamebyCoalesce,
Address From dbo.Customer

How to replace Null with Values in SQL Server - TSQL Tutorial

As you can see that we used ISNULL and replaced the Null with "Unknown Name" but it returned "Unknown Na", it did not returned complete "Unknown Name". The reason, the data type of an ISNULL expression is the data type of the first input ( Name column) that is varchar(10), that means it will always return us 10 characters.

If you have situations like this, you better use COALESCE.The data type of a COALESCE expression is the data type of the input argument with the highest data type precedence. In our case "Unknow Name" is 12 characters and it chose to use that instead of Name column data type which is varchar(10).

No comments:

Post a Comment