ISNULL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ISNULL

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-06-23 : 10:39:01

Rams writes "How ISNULL function is used in select statement"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-06-23 : 10:39:43

Declare @a int, @b int

Set @b = 1

Select isnull(@a,@b)

Corey

Kristen
Test

22859 Posts

Posted - 2004-06-24 : 01:14:42

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36429
may help

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-24 : 01:22:24

Or Books Online. That's my personal favorite.

Transact-SQL Reference

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

Expressions

IS [NOT] NULL

System Functions

WHERE

©1988-2004 Microsoft Corporation. All Rights Reserved.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources