ISNULL FUNCTION - 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 2005 Forums

Transact-SQL (2005)

ISNULL FUNCTION

Author

Topic

cmorga23
Starting Member

10 Posts

Posted - 2007-12-13 : 16:57:04

Could anyone tell how to make the isnull function return no rows if its null?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 17:02:25

WHERE YourColumn IS NOT NULL

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

cmorga23
Starting Member

10 Posts

Posted - 2007-12-13 : 17:04:02

isnull(fielname,'')

What do i put in for '' to make it run to rows.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-13 : 17:12:20

Don't use isnull (or even coalesce).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-12-14 : 02:28:43

quote:
Originally posted by cmorga23

isnull(fielname,'')

What do i put in for '' to make it run to rows.

If you want to skip the rows if fieldname is null then add

where fieldname is not null

If you want to show something in place of null, then use null or coalesce

If you are not clear on what you want, post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

caisys
Starting Member

16 Posts

Posted - 2007-12-14 : 13:39:55

quote:
Originally posted by tkizer

Don't use isnull (or even coalesce).

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Why not ???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-14 : 14:01:47

Because it doesn't do what you want. You must use WHERE YourColumn IS NOT NULL to filter the data out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-14 : 14:06:44

The reason NOT to use ISNULL or COALESCE is that you don't need it and, additionally, it will not peform as well if there is an index. Here is an example of how to runt he same query with and without a COALESCE:

DECLARE @Yak TABLE (ID INT, FileName VARCHAR(255))

INSERT @Yak
SELECT 1, 'C:\myfile.txt'
UNION ALL SELECT 2, NULL
UNION ALL SELECT 3, 'D:\Foo\Bar.xml'
UNION ALL SELECT 4, NULL

SELECT *
FROM @Yak
WHERE FileName IS NOT NULL

SELECT *
FROM @Yak
WHERE COALESCE(FileName, '') <> ''

caisys
Starting Member

16 Posts

Posted - 2007-12-19 : 07:39:33

quote:
Originally posted by tkizer

Because it doesn't do what you want. You must use WHERE YourColumn IS NOT NULL to filter the data out.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

I see, I thought you meant as a general good practice one should not use isnull or coalesce, now i c yor point, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources