Replace 0 to Null - 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)

Replace 0 to Null

Author

Topic

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-24 : 15:41:33

Hi,

I wish I could replace the sum that equals to zero for a Null in the following example :

Select Replace(SUM(intDeds),'0','Null') as [Deds]
from tbl_Data 
group by intDeds

So, if the sum of intBreak_Deds equlas 0 then I want it to return Null instead of zero.

Any idea?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-24 : 15:45:21

Use CASE for this.

CASE WHEN ... THEN ... ELSE ... END

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

Subscribe to my blog

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-24 : 15:58:31

Hi,
How about if I have the sum function on 25 columns in my query?
Shall I still use the Case for this?

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-09-24 : 16:00:14

You can alos use the NULLIF function:

DECLARE @Foo INT = 0

SELECT NULLIF(@Foo, 0)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-24 : 16:05:44

25 columns! Do this formatting in your application then.

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

Subscribe to my blog

infodemers
Posting Yak Master

183 Posts

Posted - 2010-09-24 : 16:06:33

Hi Lamprey,

You got it!

It works great, I thank you Lamprey and I thank everyone for other suggestions!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources