ISNULL
Replaces NULL with the specified replacement value.
Syntax
ISNULL ( check_expression , replacement_value )
Arguments
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 have the same type as check_expresssion.
Return Types
Returns the same type as check_expression.
Remarks
The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned.
Examples
A. Use ISNULL with AVG
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)
B. Use ISNULL
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)
See Also
'
)
|
|