-
Apr 11th, 2012, 11:49 AM
#1
Thread Starter
PowerPoster
[RESOLVED] Sometimes I want a comma, sometimes I don't
SELECT tblAddress.Address1 + ', ' + (ISNULL(tblAddress.Address2, '')) AS [Address]
How do I code that so if Address2 is null Address1 is not followed by the comma?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 11th, 2012, 12:00 PM
#2
Re: Sometimes I want a comma, sometimes I don't
Like this:
Code:
SELECT tblAddress.Address1 + (ISNULL(', ' + tblAddress.Address2, '')) AS [Address]
-
Apr 11th, 2012, 12:02 PM
#3
Re: Sometimes I want a comma, sometimes I don't
Also, in case when Address2 is '' (empty string), or one or more spaces, then you might want to do something like:
Code:
SELECT tblAddress.Address1 + (ISNULL(', ' + NULLIF(RTRIM(tblAddress.Address2), ''), '')) AS [Address]
-
Apr 11th, 2012, 12:09 PM
#4
Thread Starter
PowerPoster
Re: Sometimes I want a comma, sometimes I don't
Thank you, but I have a question.
tblAddress.Address1 + (ISNULL(', ' + tblAddress.Address2, '')) AS [Address]
Wouldn't ISNULL(', ' + tblAddress.Address2 never be true because you are prepending a comma to something that might be null, making the whole thing therefore never null?
I apologize. I have trouble understanding SQL syntax and saying it in English. I will test it in the meantime and let you know.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 11th, 2012, 12:13 PM
#5
Thread Starter
PowerPoster
Re: Sometimes I want a comma, sometimes I don't
Okay, that works perfectly, I even went with your other query in case it's spaces and not null, and that works great. But like I said, I don't understand it. Which means I never could've written this query myself
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 11th, 2012, 12:13 PM
#6
Re: Sometimes I want a comma, sometimes I don't
Anything concatenated with a NULL string becomes NULL
So when Address 2 is NULL, then ', ' + tblAddress.Address2 is also NULL, therefore there is no comma
-
Apr 11th, 2012, 12:14 PM
#7
Thread Starter
PowerPoster
Re: Sometimes I want a comma, sometimes I don't
Thanks. We cross-posted, even though I refreshed, I think I beat you!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Apr 11th, 2012, 12:16 PM
#8
Re: [RESOLVED] Sometimes I want a comma, sometimes I don't
Do you understand how it works?
[edit]Ow, OK... you marked it as resolved... I just saw that...
-
Apr 11th, 2012, 12:25 PM
#9
Thread Starter
PowerPoster
Re: [RESOLVED] Sometimes I want a comma, sometimes I don't
Yup - your explanation was very clear! Resolved, and I added to your rep. Thanks again!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|