Replacing null values with zeros

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 2008 Forums

Transact-SQL (2008)

Replacing null values with zeros

Author

Topic

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-29 : 12:02:57

Hi All,

How would you replace nulls with zeros in the output.

FYI .. we should not update the tables, just show zero instead of nulls.

Thanks in advance.

GIGABYTE+

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 12:07:49

[code]
SELECT COALESCE(yourcol,0) AS colname FROM table

or

SELECT ISNULL(yourcol,0) AS colname FROM table
[/code]

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

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-29 : 14:02:27

Thanks Visakh16.

Do we have any other ways to do this?

GIGABYTE+

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-05-29 : 14:06:42

There's also a CASE expression:

SELECT CASE WHEN yourcol IS NULL THEN 0 ELSE yourcol END FROM table

This is exactly what COALESCE does, just more typing.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 14:09:06

quote:
Originally posted by Gigabyte

Thanks Visakh16.

Do we have any other ways to do this?

GIGABYTE+

is that an interview question?

Why the above two methods wont work for you?

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

Gigabyte
Starting Member

30 Posts

Posted - 2012-05-29 : 14:14:01

Above two query's worked perfect.

As I was aware them I was curious to know other methods.

With that curiosity, I learned that we can embed case statement in select query.

Thanks Visakh16

GIGABYTE+

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-29 : 14:30:25

quote:
Originally posted by Gigabyte

Above two query's worked perfect.

As I was aware them I was curious to know other methods.

With that curiosity, I learned that we can embed case statement in select query.

Thanks Visakh16

GIGABYTE+

Ok just checked
Actually as Rob pointed out COALESCE is shorthand version of CASE WHEN

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources