SQL Server ISNULL Function - Replace NULL Values
SQL Server ISNULL()
function replaces the NULL value with a given replacement expression.
ISNULL(input_expression, replacement_value)
Parameters
input_expression: This is the expression to be checked for a NULL value. It can be of any type.
replacement_value: value that is returned if the input_expression evaluates to NULL.
Return Value
Returns the same type as the input expression.
If the input_expression
is not null then its value is returned else the replacement value is returned.
Note: If a literal NULL is provided as the input_expression
, then ISNULL()
returns the datatype of the replacement_value
.
If a literal NULL is provided as the replacement_value
and no replacement value is provided, then ISNULL()
returns an integer value.
Example 1:
The following example demonstrates the ISNULL()
function.
SELECT ISNULL(NULL, 'Hello') AS Result
![](../../Content/images/sqlserver/isnull1.png)
The ISNULL()
function returns the input expression value if it is not a NULL.
In the following example, the given expression 'SQL Server' is not a null value and hence ISNULL returns the expression as it is without replacing it.
SELECT ISNULL('SQL Server', 'abcd') AS Result;
![](../../Content/images/sqlserver/isnull2.png)
Now, consider the following Employee
table where two rows has NULL value in the DepartmentID
column.
![](../../Content/images/sqlserver/isnull3.png)
In the following example, the ISNULL()
replaces all the NULL value in the DepartmenID
column with 20
.
SELECT EmployeeID, FirstName, LastName,
ISNULL(DepartmentID, 20) As Department FROM Employee;
![](../../Content/images/sqlserver/isnull4.png)