ISNULL (Transact-SQL)
Replaces NULL with the specified replacement value.
Transact-SQL Syntax Conventions
- check_expression
-
Is the expression to be checked for NULL. check_expression can be of any type.
- replacement_value
-
Is the expression to be returned if check_expression is NULL. replacement_value must be of a type that is implicitly convertible to the type of check_expresssion.
A. Using ISNULL with AVG
The following example finds the average of the weight of all products. It substitutes the value 50 for all NULL entries in the Weight column of the Product table.
USE AdventureWorks; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO
Here is the result set.
-------------------------- 59.79 (1 row(s) affected)
B. Using ISNULL
The following example selects the description, discount percentage, minimum quantity, and maximum quantity for all special offers in AdventureWorks. If the maximum quantity for a particular special offer is NULL, the MaxQty shown in the result set is 0.00.
USE AdventureWorks; GO SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' FROM Sales.SpecialOffer; GO
Here is the result set.
Description DiscountPct MinQty Max Quantity --------------- ------------- -------- --------------- No Discount 0.00 0 0 Volume Discount 0.02 11 14 Volume Discount 0.05 15 4 Volume Discount 0.10 25 0 Volume Discount 0.15 41 0 Volume Discount 0.20 61 0 Mountain-100 Cl 0.35 0 0 Sport Helmet Di 0.10 0 0 Road-650 Overst 0.30 0 0 Mountain Tire S 0.50 0 0 Sport Helmet Di 0.15 0 0 LL Road Frame S 0.35 0 0 Touring-3000 Pr 0.15 0 0 Touring-1000 Pr 0.20 0 0 Half-Price Peda 0.50 0 0 Mountain-500 Si 0.40 0 0 (16 row(s) affected)
