Use of the isnull function in SQL-replace null with the specified replacement value

Isnull

Replace null with the specified replacement value.

Syntax

Isnull(Check_Expression, Replacement_value)

Parameters

Check_expression

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

Replacement_value

InCheck_expressionReturns the expression if it is null.Replacement_valueMust beCheck_expresssionHas the same type.

Return type

Returns andCheck_expressionThe same type.

Note

IfCheck_expressionIf it is not null, the value of this expression is returned; otherwiseReplacement_value.

Example A. Use isnull With AVG

The following example finds the average price of all books and replaces it with the value $10.00.TitlesTablePriceAll null entries in the column.

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. Use isnull

The following example showsTitlesSelect the title, type, and price for all the books in the table. If the price for a title is null, 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)