What is ISNull and Limitations of ISNull Function in SQL Server - TSQL Tutorial

IsNull() is TSQL Function that we use to replace the Null value with desired value. We create different columns by using different data types in a table and often we define them to accept Null value. While we select the data from table/s for different purposes such as reports or in applications, we want to replace these Null values with some values as required.

--Create Sample Table
USE TestDB
go

  Create table dbo.CustomerAddress (
  FName VARCHAR(100),
  LName VARCHAR(100),
  HouseNumber Integer,
  StreetName VARCHAR(100),
  City VARCHAR(100),
  [State] CHAR(2),
  IsActive BIT)
  go

--Insert some sample data
  insert into dbo.CustomerAddress
  values ('Aamir','Shahzad',123,'Test Street','Charlotte','NC',1)
  go
  insert into dbo.CustomerAddress
  values ('John','Smith',43,'ELM Street','Charlotte','NM',0)
  go
  insert into dbo.CustomerAddress
  values ('Mike','Rivers',67,'East West Street','Charlotte',NULL,NULL)

--Select the data from table. Create a new Column by concatenating columns and 

--see the output if any value is Null
 Select *,Cast(HouseNumber AS  VARCHAR(10)) +' '+StreetName+' '+City+' '+State 

 AS FullAddress
 from dbo.CustomerAddress

--To see if Concat_Null_Yields_Null is set ( If SETs then concatenation will produce 

--Null outcome)
 dbcc useroptions

--We can set to OFF by using below statement. Once Set to Off , The Null part will be 

--ignore and outcome of concatenation will be string concatenation 
 SET concat_null_yields_null OFF


--Use ISNULL function to replace Null with blank
 Select *,Cast(HouseNumber AS  VARCHAR(10)) +' '+StreetName+' '+City+' '+ISNULL(State,'') 

AS FullAddress,
 ISNULL(State,'Unknow')  AS StateAvailableOrNot
 from dbo.CustomerAddress


--ISNull provides output according to the data type of column used in ISNull function, 
--Instead of using Isnull use case statement to produce Unknow value for Null values.
 Select *,Cast(HouseNumber AS  VARCHAR(10)) +' '+StreetName+' '+City+' '+ISNULL(State,'') 

 AS FullAddress,
 Case
 When State is null  Then 'Unknown' ELSE State END AS StateAvailableOrNot
 from dbo.CustomerAddress


--Use Coalesce function instead of using ISNULL 
  Select *,Cast(HouseNumber AS  VARCHAR(10)) +' '+StreetName+' '+City+' '+ISNULL(State,'') 

  AS FullAddress
 ,ISNULL(State,'Uknown') StateAvailableOrNot_ISNULL,
  Coalesce(State,'Uknown') StateAvailableOrNot_Coalesce
  from dbo.CustomerAddress