Concatenating fields with null values

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)

Concatenating fields with null values

Author

Topic

waxdart23
Starting Member

33 Posts

Posted - 2004-06-18 : 04:08:28

I am currently trying to concatenate 4 columns into one blank column using the following:

UPDATE MASTER_GROUP
SET RMEMO = INFO1 + ' ' + INFO2 + ' ' + INFO3 + ' ' + INFO4

However in most cases some of the columns have NULL values, in these cases the update isnt working. It only concatenates rows where there is data in all four 'INFO' fields. Can anyone help?

Thanks
P

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-18 : 04:24:52

Null + something = null

For concatanation you can use the isnull function to substitute the null values with a given value.

eg If you want to substitute the null values with an empty string then this should do it.

select isnull('ABC', '') + isnull(ColumnWithNullValue, '') + isnull('DEF', '') +
isnull(AnotherColumnWithNullValue, '')
from mytable

Hopefully that makes sense

Duane.

waxdart23
Starting Member

33 Posts

Posted - 2004-06-18 : 04:40:22

Works a treat... many thanks for your help Duane.

Thanks
P

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 09:08:46

This can help too.

set concat_null_yields_null off

select 'abc' + 'def' + NULL + 'ghi' as Test

set concat_null_yields_null on


Test      
--------- 
abcdefghi

(1 row(s) affected)

Kristen
Test

22859 Posts

Posted - 2004-06-18 : 13:48:41

quote:
Originally posted by ditch

For concatanation you can use the isnull function to substitute the null values with a given value.

Is it just me that hates the ISNULL() function?

Its whole name suggest that it is going to return a TRUE/FALSE value for whether something is NULL

Not sure COALESCE() is a better name, but I suppose its ability to take a variable length parameter list is a bonus

Its a bit like the subtle differences of LEN() and DATA_LENGTH()

Kristen

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 14:34:41

COALESCE is ANSI

ISNULL is not

Preston, you're scaring me man...

Never play with settings unless the box is on fire and there's no way out...

Then still don't

MOO

Brett

8-)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 15:06:06

I just wanted to type SET CONCAT_NULL_YIELDS_NULL

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-18 : 15:09:20

I was wondering why those PRD reports looked funky....

Brett

8-)

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-06-18 : 15:11:00

You don't know the half of it.

Kristen
Test

22859 Posts

Posted - 2004-06-19 : 03:00:03

quote:
Originally posted by X002548

COALESCE is ANSI

ISNULL is not

Really? That's fantastic, I thought it was the other way round so I've been allowing ISNULL(), but now they've got no excuse.

Public floggings will being on Monday at 9AM ...

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-20 : 09:35:28

quote:
Originally posted by Kristen

Is it just me that hates the ISNULL() function?

Its whole name suggest that it is going to return a TRUE/FALSE value for whether something is NULL

That's a good point Kristen.
We use it very often at our shop - but fortunately for us it hasn't really caused any confusion yet.

Duane.

Kristen
Test

22859 Posts

Posted - 2004-06-20 : 12:01:42

Apart from the fact that I think the name is a poor choice (but we're stuck with that of course), I try to encourage use of COALESCE (which has a similar suffering in that it is a word that most people I come across cannot describe the meaning of!) because it allows any number of parameters
e.g. COALESCE(WarehouseStock, ShopStock, 0)
and therefore I prefer its use but at the same time I don't want two different functions doing very similar jobs.

But as I thought that IsNull() was the ANSI standard and COALESCE was a Microsoft/Sybase extension, I let it go.

But thanks to my [now!!] Very Dear Friend Brett the error in my memory is fixed and all use of ISNULL is banned forthwith!

As they say around these parts: "We've talked about it for 5 minutes and now we're going to do it Kristen's way" :)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-20 : 12:37:22

As they say around these parts: "We've talked about it for 5 minutes and now we're going to do it Kristen's way" :)

As they say around these parts: "We've talked about it for 5 minutes and Derrick decided we're going to do it this way." :)
--I like that variation slightly better. lol

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-06-20 : 13:41:25

I'd like it too, but they don't say it like that around here ... :)

Kristen

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-21 : 07:30:07

quote:
Originally posted by derrickleggett
As they say around these parts: "We've talked about it for 5 minutes and Derrick decided we're going to do it this way." :)

Which way is that ? - "The Mean old dba way"

Thanks for all the input here guys - I'm sold on this way of thinking now too

Duane.

X002548
Not Just a Number

15586 Posts

Posted - 2004-06-21 : 10:03:53

Or as they say around here...

We've talked about it for an hour, dismissed Brett's ideas...until another hour has passed, then someone regurgatates the exact same concept, and then it's the way to go.....

glad I could help...

Brett

8-)

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-21 : 10:12:44

When I said thanks for all the input - I did not exclude you Brett

Yeah, I'm glad you brought the fact that isnull is not ANSI to our attention.

I have not used the coallesce function before, but now after trying it out I've realised that I've missed out big time.

Thanks again.

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources