Replaces NULL with the specified replacement value.
ISNULL(check_expression, replacement_value)
Returns the same type as check_expression.
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.
This example finds the average of the prices of all titles, substituting the value $10.00 for all NULL entries in the price column of the titles table.
USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO
Here is the result set:
--------------------------
14.24
(1 row(s) affected)
This example selects the title, type, and price for all books in the titles table. If the price for a given title is NULL, the price shown in the result set is 0.00.
USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO
Here is the result set:
Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
You Can Combat business 2.99
Straight Talk A business 19.99
Silicon Valley mod_cook 19.99
The Gourmet Mic mod_cook 2.99
The Psychology UNDECIDED 0.00
But Is It User popular_comp 22.95
Secrets of Sili popular_comp 20.00
Net Etiquette popular_comp 0.00
Computer Phobic psychology 21.59
Is Anger the En psychology 10.95
Life Without Fe psychology 7.00
Prolonged Data psychology 19.99
Emotional Secur psychology 7.99
Onions, Leeks, trad_cook 20.95
Fifty Years in trad_cook 11.95
Sushi, Anyone? trad_cook 14.99
(18 row(s) affected)
Expressions | WHERE |
IS [NOT] NULL | System Functions |