MSSQL isnull function [Abstract]

Document directory

• A. Use isnull With AVG
• B. Use isnull
• C. Test null in the WHERE clause.

Isnull (TRANSACT-SQL)

 

Replace null with the specified replacement value.

Transact-SQL syntax conventions

Syntax

ISNULL ( check_expression , replacement_value )

Parameters

Check_expression

Whether the expression is null is checked. Check_expression can be of any type.

Replacement_value

The expression to be returned when check_expression is null. Replacement_value must be a type that can be implicitly converted to the check_expresssion type.

Return type

Returns the same type as check_expression.

Note

If check_expression is not null, its value is returned. Otherwise, the former is returned after the replacement_value is implicitly converted to the check_expression type (if the two types are different.

Example
A. Use isnull With AVG

The following example shows the average weight of all products. It replaces all null items in the weight column of the product table with a value of 50.

Copy code

USE AdventureWorks2008R2; GO SELECT AVG(ISNULL(Weight, 50)) FROM Production.Product; GO

The following is the result set:

--------------------------

59.79

 

(1 row (s) affected)

B. Use isnull

In the following example, the description, discount percentage, minimum quantity, and maximum quantity of all special offer products in adventureworks2008r2 are selected. If the maximum number of special offers is null, The maxqty shown in the result set is 0.00.

Copy code

USE AdventureWorks2008R2; GO SELECT Description, DiscountPct, MinQty, ISNULL(MaxQty, 0.00) AS 'Max Quantity' FROM Sales.SpecialOffer; GO

The following is the result set:

Description discountpct minqty max quantity

---------------------------------------------------

No diskcount 0.00 0 0

Volume Discount 0.02 11 14

Volume Discount 0.05 15 4

Volume Discount 0.10 25 0

Volume diskcount 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 frames 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)

C. Test null in the WHERE clause.

Do not use isnull to find null values. Instead, use is null. The following example finds all products with null in the weight column. Note the space between is and null.

Copy code

USE AdventureWorks2008R2; GO SELECT Name, Weight FROM Production.Product WHERE Weight IS NULL; GO

See
Reference expression (TRANSACT-SQL) is [not] null (TRANSACT-SQL) system function (TRANSACT-SQL) Where (TRANSACT-SQL) coalesce (TRANSACT-SQL)