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 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