concatenating column values in select when there is a null column

Question

Post reply

concatenating column values in select when there is a null column

new coder

Right there with Babe

Points: 791
More actions
January 15, 2009 at 8:05 am

#126758

I have a select statement like:
select people.last + ',' +people.first + ', ' + address.street + ', ' + address.city + ', ' + address.zipcode + ) as info
When one of the columns has a null value, the select return null. I would like to have other columns that are not null. How can I do this?!
Thanks.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Chad Crawford SSChampion Points: 11610 More actions · Answer 1

use the ISNULL() function to replace the null values with an empty string.

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 2

I would also look at the COALESCE() function. This allows more than one arguement to be evaluated and is also the ansi standard. But isnull also works!

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

You have to use the function COALESCE or ISNULL (I prefer using COALESCE)....

SELECT COALESCE( people.last, '') + ',' + COALESCE( people.first, '' ) + ', ' + COALESCE( address.street, '' ) + ', ' +

COALESCE( address.city, '' ) + ', ' + COALESCE( address.zipcode, '' ) + COALESCE( [Wink], '' ) AS Info

FROM people

Edit:

I was beaten up by the guys above....:cool:

--Ramesh

ps. One Orange Chip Points: 29422 More actions · Answer 4

use ISNULL

select isnull(people.last,'') + ',' +isnull(people.first,'') + ', ' + isnull(address.street ,'')+ ', ' + isnull(address.city,'') + ', ' + isnull(address.zipcode,'') as info

Pradeep Singh

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 5

dmc (1/15/2009)
I would also look at the COALESCE() function. This allows more than one arguement to be evaluated and is also the ansi standard. But isnull also works!

I wouldn't use COALESCE if you can avoid it... it's actually a fair bit slower than ISNULL...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 6

True... it is slightly (very slightly) faster. And in billions of rows it could make a difference. But is it wroth compliance of standards and advantages of functionality of coallesce to go to isnull? Thats the debate.... I never think one as always right or wrong, but unless you need that minimal gain it seems the other benefits of coalesce exceed that of isnull.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 7

dmc (1/15/2009)
True... it is slightly (very slightly) faster. And in billions of rows it could make a difference. But is it wroth compliance of standards and advantages of functionality of coallesce to go to isnull? Thats the debate.... I never think one as always right or wrong, but unless you need that minimal gain it seems the other benefits of coalesce exceed that of isnull.

Nope... no debate... the idea of truly portable code using only ANSI standard code will always be a myth and you shouldn't even try for it in batch code... the extensions in each vendor's RDBMS are just too valuable not to use because of some silly standard that will never catch up with real life. GUI code? Maybe if you want to limit yourself to simple C.R.U.D.... but that's about it.

It doesn't take billions of rows to show much of a difference although, I agree, some don't consider the difference between the two to be worthwhile because of the rest of their code is so slow. 😉 Consider this... if you can save 100 ms (for example) on every hundred thousand rows and you need to process 100 million rows (not total table size... just rows touched in a batch... sometimes they're the same rows), that's 100 seconds of time you saved on a large batch run... now, do that in 20 different places in that same batch code and suddenly you've just saved more than 30 minutes of run time.

You can spend a whole lot of time making truly portable code and still might (probably) fail. I'd rather worry about making absolutely the fastest code possible and if I need to migrate it to some other platform, I'll warp or even rewrite the code to use all of the extensions to performance I can. What the hell... it's gonna have to go through full regression testing because you changed the platform, anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Garadin One Orange Chip Points: 29613 More actions · Answer 8

dmc (1/15/2009)
True... it is slightly (very slightly) faster. And in billions of rows it could make a difference. But is it wroth compliance of standards and advantages of functionality of coallesce to go to isnull? Thats the debate.... I never think one as always right or wrong, but unless you need that minimal gain it seems the other benefits of coalesce exceed that of isnull.

"Advantages of Functionality" are not really an arguable distinction between the two. If you need to evaluate more than 1 value at a time for a NULL, then you use coalesce. If you don't, you can use ISNULL instead. The fact that COALESCE *can* do something extra doesn't make it any more fitting to use in a scenario when you *don't* need to do that something extra. If your situation changed, you'd have to change the values you were evaluating anyways, and you could hop over to COALESCE at that time.

ANSI standard compliance could possibly be an arguable reason to use COALESCE if the rest of your code adhered to these standards, but as far as code updates go, ISNULL -> COALESCE is a pretty easy find and replace fix as the syntax of the two commands is the same. (And while I'll say that I agree that it's an "arguable" point, I'm on the same wavelength as Jeff on this one)

I personally use both. I use COALESCE when I'm using two actual values (Such as COALESCE(A.Value, B.Value) ), and ISNULL when I'm just NULL checking (for situations like the OP's).

That said, I do love the word COALESCE.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 9

Haha... I agree that truely portable code is almost impossible to achieve. 🙂 But working in an environment with multiple systems we try to make our code as portable as possible. I like this arguement because both work (and I use both as the last poster indicated), and never said that ISNULL was wrong. I think I simply wanted to point out that you should not just accept isnull as the answer because its not the only option and is not always the best. I will 100% agree if you are seeking performance for a single value is null then isnull wins hands down.

And not to open the debate into another realm.. but if you want to talk performance should he just get rid of the nullable column? hehe...

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

Garadin (1/15/2009)
dmc (1/15/2009)
True... it is slightly (very slightly) faster. And in billions of rows it could make a difference. But is it wroth compliance of standards and advantages of functionality of coallesce to go to isnull? Thats the debate.... I never think one as always right or wrong, but unless you need that minimal gain it seems the other benefits of coalesce exceed that of isnull.
"Advantages of Functionality" are not really an arguable distinction between the two. If you need to evaluate more than 1 value at a time for a NULL, then you use coalesce. If you don't, you can use ISNULL instead. The fact that COALESCE *can* do something extra doesn't make it any more fitting to use in a scenario when you *don't* need to do that something extra. If your situation changed, you'd have to change the values you were evaluating anyways, and you could hop over to COALESCE at that time.
ANSI standard compliance could possibly be an arguable reason to use COALESCE if the rest of your code adhered to these standards, but as far as code updates go, ISNULL -> COALESCE is a pretty easy find and replace fix as the syntax of the two commands is the same. (And while I'll say that I agree that it's an "arguable" point, I'm on the same wavelength as Jeff on this one)
I personally use both. I use COALESCE when I'm using two actual values (Such as COALESCE(A.Value, B.Value) ), and ISNULL when I'm just NULL checking (for situations like the OP's).
That said, I do love the word COALESCE.

I seem to recall another thread where ISNULL was used and returned an error in type conversion (implicit conversion was being done if I remember). When ISNULL was changed to COALESCE, the error went away. I wish I could remember where that thread was or what combination of data types caused the error. I just tried a few combinations myself and the ones I did worked.

Anyone else remember something like this?

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 11

That is true. But it can work the other way as well. ISNULL keeps the original data type. So if it is 2 characters and you isnull(column(char(2), 'some longer string') it will truncate, which is bad. However you could technically say that the fact that coalesce takes the higher datatype and displays the full text as bad since its no longer 2 characters. I usually hate the truncation issue, but growing a data type is not good either.

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

dmc (1/15/2009)
Haha... I agree that truely portable code is almost impossible to achieve. 🙂 But working in an environment with multiple systems we try to make our code as portable as possible. I like this arguement because both work (and I use both as the last poster indicated), and never said that ISNULL was wrong. I think I simply wanted to point out that you should not just accept isnull as the answer because its not the only option and is not always the best. I will 100% agree if you are seeking performance for a single value is null then isnull wins hands down.
And not to open the debate into another realm.. but if you want to talk performance should he just get rid of the nullable column? hehe...

BWAA-HAA!!! Now, that would be the way to do it even if you stored and empty string!

And, sorry... didn't mean to start a bash on portable code... it's just a bit of a sore spot with me. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dmc-608719 SSCarpal Tunnel Points: 4663 More actions · Answer 13

No problem Jeff... I like a good debate! Its what makes the job fun and makes you rethink your basis of how you handle solutions to issues in the future.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Jeff Moden (1/15/2009)
dmc (1/15/2009)
Haha... I agree that truely portable code is almost impossible to achieve. 🙂 But working in an environment with multiple systems we try to make our code as portable as possible. I like this arguement because both work (and I use both as the last poster indicated), and never said that ISNULL was wrong. I think I simply wanted to point out that you should not just accept isnull as the answer because its not the only option and is not always the best. I will 100% agree if you are seeking performance for a single value is null then isnull wins hands down.
And not to open the debate into another realm.. but if you want to talk performance should he just get rid of the nullable column? hehe...
BWAA-HAA!!! Now, that would be the way to do it even if you stored and empty string!
And, sorry... didn't mean to start a bash on portable code... it's just a bit of a sore spot with me. 😛

Actually Jeff, I know your sore spot.