January 15, 2009 at 8:05 am
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.
January 15, 2009 at 8:09 am
use the ISNULL() function to replace the null values with an empty string.
January 15, 2009 at 8:11 am
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!
January 15, 2009 at 8:11 am
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
January 15, 2009 at 8:12 am
use ISNULL
select isnull(people.last,'') + ',' +isnull(people.first,'') + ', ' + isnull(address.street ,'')+ ', ' + isnull(address.city,'') + ', ' + isnull(address.zipcode,'') as info
January 15, 2009 at 8:44 am
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
January 15, 2009 at 9:03 am
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.
January 15, 2009 at 9:22 am
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
January 15, 2009 at 9:23 am
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.
January 15, 2009 at 9:38 am
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...
January 15, 2009 at 9:41 am
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?
January 15, 2009 at 9:50 am
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.
January 15, 2009 at 9:50 am
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
January 15, 2009 at 9:52 am
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.
January 15, 2009 at 9:53 am
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.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply