ISNULL (Transact-SQL)

Replaces a NULL value with a specified replacement value.

Syntax


ISNULL ( check_expression , replacement_value )

Arguments

check_expression

Is the expression to check for NULL values.

replacement_value

Is the expression to return if check_expression is NULL. replacement_value must be of a data type that is compatible with the data type of check_expression.

Return Type

Returns the same data type as check_expression.

Permissions

Any user can execute the ISNULL function.

Examples

A. Using ISNULL to replace NULL values in a query

The following example returns the value of the Bonus column from the Sales table. If the Bonus value is NULL, it returns 0.


USE AdventureWorks2019;
GO
SELECT JobTitle, ISNULL(Bonus, 0) AS Bonus
FROM HumanResources.Employee
WHERE JobTitle LIKE 'Production%';
GO

B. Using ISNULL with string concatenation

The following example returns the MiddleName or a space if MiddleName is NULL.


USE AdventureWorks2019;
GO
SELECT FirstName + ' ' + ISNULL(MiddleName, ' ') + ' ' + LastName AS Name
FROM Person.Person
WHERE LastName = 'Smith'
ORDER BY FirstName;
GO

C. Using ISNULL with implicit conversion

If check_expression is of a data type that cannot be implicitly converted to the data type of replacement_value, an error will be returned.


USE AdventureWorks2019;
GO
SELECT ISNULL(TerritoryID, 'N/A')
FROM Sales.SalesTerritory;
GO

Warning: The preceding statement will fail because the integer data type of TerritoryID cannot be implicitly converted to the character string data type of 'N/A'.

Remarks

ISNULL uses the same functionality as the ANSI SQL function COALESCE. For information about the syntax and usage of COALESCE, see COALESCE (Transact-SQL).
ISNULL evaluates the first expression. If the first expression is not NULL, the value of the first expression is returned. Otherwise, the second expression is evaluated and returned.
The data type of the value returned by ISNULL is the data type of the check_expression.
If both check_expression and replacement_value are character strings, the result will have the same data type and length as check_expression. If they are different character string types, the rules for concatenated character strings apply.
If both check_expression and replacement_value are not character strings, they must have the same data type, or the data type of check_expression must be implicitly convertible to the data type of replacement_value.