Coalesce... but with zero instead of NULL

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

General SQL Server Forums

New to SQL Server Programming

Coalesce... but with zero instead of NULL

Author

Topic

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-29 : 20:29:40

I want to display an integer only if its bigger than 0 in order to give more visibility to that few cases in a chart packed with numbers that are not zero. Off course I could do it with a Case when... but I look for something smarter.

Something like COALESCE but not with NULL but with zero '0'; that returns a number only if it is NOT zero, and otherwise it returns NULL

Any solution for that?
Martin

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 20:44:55

you mean this?

NULLIF([YourColumn],0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-29 : 20:52:30

smarter doesn't necessary mean faster or more efficient.

For your requirement, using a case when is the easiest way

KH
[spoiler]Time is always against us[/spoiler]

barnabeck
Posting Yak Master

236 Posts

Posted - 2012-07-29 : 21:27:05

Visakh, I have been trying this in all kind of flavors with NULL, '0', 0... and it didn't work. Now it does...!!? maybe I got confused in chains of casts or I just have to go to bed...
Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-29 : 21:56:57

quote:
Originally posted by barnabeck

Visakh, I have been trying this in all kind of flavors with NULL, '0', 0... and it didn't work. Now it does...!!? maybe I got confused in chains of casts or I just have to go to bed...
Thank you

np

you're welcome

glad that i could sort it out for you

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-07-29 : 22:16:25

here is a "smart" way of doing it without CASE

nullif(sign(num), -1) * num

or if you also want to return NULL when the value is zero

nullif(nullif(sign(num), -1) * num, 0)

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources