:::: MENU ::::

Use ISNULL to replace NULL value with alternate

  • Apr 10 / 2009
  • 0
dbDigger, Null Values, T-SQL Tips and Tricks, User Defined Functions UDF

Use ISNULL to replace NULL value with alternate

It may be required to change NULL value of a column with a valid alternate value. This scenario is mostly there for report generation purpose. T-SQL provide a very handy function ISNULL() to replace NULL value with alternate value. Following is syntax for using ISNULL()
ISNULL(ColumntoCheckForNULL, AlternateValue)
ISNULL accepts two parameters. First parameter will always be the column for which alternate is required in case of its NULL value. Second parameter is the alternate value that is required to replace the NULL value. Second value may be any other column or other value. Alternate value may be implicitly converted to data type of first parameter. For example you may provide datetime as alternate value for character family data type but you can not provide character family data type as alternate of date time value.
For example if we are required to display City as alternate of NULL value of AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,city), PostalCode  
 FROM Person.address  

Similarly to display a string of characters as alternate of NULL AddressLine2

 USE AdventureWorks  
 GO  
 SELECT AddressLine1, ISNULL(AddressLine2,'No Address Line 2'), PostalCode  
 FROM Person.address  

This user defined function would be very helpful while working with string concatenations. If one of the strings being concatenated is NULL then end result of concatenation would be NULL. To avoid this situation use ISNULL where NULL value is expected.Just consider following two examples for use and effect of ISNULL in string concatenation.

 
-- String concatenation with NULL value  
 SELECT 'Value1 '+'Value2 '+NULL AS ConcatenatedString  
 GO  
 -- String concatenation by using ISNULL  
 SELECT 'Value1 '+'Value2 '+isnull(NULL, 'NULL-Replaced') AS ConcatenatedString  
 GO  
Consult us to explore the Databases. Contact us