Adding columns where some are 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

SQL Server 2000 Forums

Transact-SQL (2000)

Adding columns where some are NULL

Author

Topic

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-02-18 : 14:21:13

Hello,

I'm sure this is in the archives somewhere, but I couldn't seem to find it. I'm working with a database (I didn't design) that is kind of ugly. It's rather flat. I have a bunch of columns in a row that I need to add, but the default value is null. How do I disregard the null values and add the columns that do have a value?

This is what I have, but when there is a null value my value is null.


SELECT column1 + column2 + column3 + column4 + column5 + column6 + column7 + column8 + column8 + column10 FROM someTable

*Note - there should be plus signs between the columns. I guess I'm just a moron and can't get them to appear.
When I did a preview the + signs weren't appearing.

Any help is greatly appreciated.

Thanks,
Nick

Edited by - nickfinity on 02/18/2003 16:19:48

MichaelP
Jedi Yak

2489 Posts

Posted - 2003-02-18 : 14:39:01

COALESCE() is the function you are looking for.
IF you are trying to "string concatinate", the second value should be empty string. If you are trying to do a math add, use 0 instead.


SELECT COALESCE(column1, '') + COALESCE(column2, '') FROM someTable

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-18 : 15:09:14

Or, if like me, you have trouble spelling 'COALESCE', you can use the ISNULL() function in the same way in this particular case.

Since I think you said you are adding, though, you probably want to use

ISNULL(Field1,0) or COALESCE(Field1,0)

instead of using '' as your null replacement.

EDIT: Ooops; Sorry Michael, didn't see that you mentioned using 0 as an option already ....

- Jeff

Edited by - jsmith8858 on 02/18/2003 15:13:22

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 15:19:34

Just to take this to the nth level, what do you use as a default value for a dattime column? We've dealt with many variations by converting to varchar first then pass in an empty string. When we've had to do DateDiff, or any other date based function the s/p would have to check the value first, ie:

If IsDate(col1) = 1
BEGIN
Select @X=DATEDIFF(day,col1,7)
END

Hope this helps

Brett 8-)

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-02-18 : 16:09:18

Thanks everyone. This site is the greatest!

nickfinity
Yak Posting Veteran

55 Posts

Posted - 2003-02-18 : 16:26:56

X002548,

I'm not sure what they are using as defaults for dates. Unfortunately this wasn't a SPROC. The company I work for has an application that publishes information out of databases and normally we just use straight queries instead of SPROCs.

Thanks again,
Nick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources