SQL: isnull

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 pubsgoselect AVG (isnull (price, $10.00) from titlesgo
 

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 pubsgoselect substring (title, 1, 15) as title, type as type, isnull (price, 0.00) as pricefrom titlesgo
 

The following is the result set:

Title type price subject ------------ submit the busy execut business 19.99 cooking with Co Business 11.95you can combat business 2.99 straight talk a business 19.99 Silicon Valley mod_cook 19.99the gourmet mic mod_cook 2.99the psychology undecided 0.00but is it user needed 22.95 secrets of sili popular_comp 000000net etiquette popular_comp 0.00 computer phobic Psychology 21.59is 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 bytes ty years in trad_cook 11.95 sushi, anyone? Trad_cook 14.99 (18 row (s) affected)