SQL Server useful functions

server|sqlserver| function <!--#include file= "./news/const.asp"-->isnull
Replaces NULL with the specified replacement value.
Grammar
ISNULL (Check_expression, Replacement_value)
Parameters
Check_expression

An expression that will be checked for null. Check_expression can be of any type.

Replacement_value

An expression to be returned when check_expression is null. Replacement_value must have the same type as check_expresssion.
return type
Returns the same type as check_expression.
Comments
Returns the value of the expression if check_expression is not NULL, otherwise return replacement_value.
Example A. Use ISNULL with AVG
The following example finds the average price of all books and replaces all NULL entries in the prices column of the titles table with the value $10.00.

Use Pubsgoselect AVG (ISNULL (Price, $10.00)) from Titlesgo

Here is the result set:

--------------------------14.24 (1 row (s) affected)
B. Use of ISNULL
The following example selects the title, type, and price for all the books in the titles table. If the price of a title is NULL, the price shown in the result set is 0.00.

Use Pubsgoselect SUBSTRING (title, 1,) as title, type as type, ISNULL (Price, 0.00) as Pricefrom Titlesgo

Here is the result set:

Title Type Price-----------------------------------------------------the Busy execut business 19.99 Cooking with Co bu Siness 11.95 You Can combat business 2.99 Straight Talk A business 19.99 Silicon Valley mod_cook the 19.99 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 Etiquett E popular_comp 0.00 Computer phobic Psychology 21.59 are anger the En psychology 10.95 Life without Fe psychology 7.00 Prol onged 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 (s) affected)