Return 0 if null if not leave the value

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

Return 0 if null if not leave the value

Author

Topic

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-09 : 10:04:26

I uploaded an excel file into my sandbox and in some of the columns there was no data, so it returned a null value, I am then use a case statement to change the null to 0, but now if there was a value in the field it comes back as null, so I am wanting to know how to pull make the null values to 0 but if it has a value in the field to leave it as is.

looks like this

ASD BR BRF E MD O P S U

1 3 4 5
1 3 3

-----------------------------------------------------
I want it to look like this

0 0 0 0 0 0 0 0 0
0 1 0 0 3 0 4 0 0
1 0 0 3 0 0 3 0 0

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-09 : 10:16:56

ISNULL or COALESCE in your select statement.

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-09 : 10:23:42

not exactly sure what you mean, what I have written is

case when [Assigned Servicer Different] is null then 0 End as [Assigned Service Different]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-09 : 10:28:15

You need an else clause in your case expression like this:

case when [Assigned Servicer Different] is null then 0 ELSE [Assigned Servicer Different] End as [Assigned Service Different]

That is logically equivalent to this, which is what Don was suggesting:

COALESCE([Assigned Servicer Different],0) AS [Assigned Servicer Different]

FightingTiger
Starting Member

23 Posts

Posted - 2012-05-09 : 10:29:28

Okay that makes sense, I am still trying to learn the use of coalesce, but that does makes sense now, thanks to both.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources