Isnull
Replace NULL with the specified replacement value.
grammar
ISNULL (Check_Expression, Replacement_Value)
parameter
Check_expression
Will be checked whether it is NULL expression. Check_expression can be any type.
Replacement_Value
The expression will return when check_expression is NULL. Replacement_Value must have the same type as Check_expressSION.
Return type
Returns the same type as check_expression.
Comment
If check_expression is not null, then returns the value of the expression; otherwise returns Replacement_Value.
Example
A. Use ISNULL with AVG
The following example looks out the average price of all books, replacing all Null entries in the Price column of the Titles table with a value of $ 10.00.
USE PUBS
Go
SELECT AVG (ISNULL (Price, $ 10.00))
From titles
Go
The following is the result set:
----------------------------
14.24
(1 row (s) affected)
B. Using ISNULL
The following example selection book name, type, and price of all books in the Titles table. If the price of a book name is NULL, then the price displayed 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
The following 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)