[RESOLVED] Sometimes I want a comma, sometimes I don't

[MMock]

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?

[CVMichael]

Like this:

SELECT tblAddress.Address1 + (ISNULL(', ' + tblAddress.Address2, '')) AS [Address]

[CVMichael]

Also, in case when Address2 is '' (empty string), or one or more spaces, then you might want to do something like:

SELECT tblAddress.Address1 + (ISNULL(', ' + NULLIF(RTRIM(tblAddress.Address2), ''), '')) AS [Address]

[MMock]

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.

[MMock]

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

[CVMichael]

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

[MMock]

Thanks. We cross-posted, even though I refreshed, I think I beat you!

[CVMichael]

Do you understand how it works?

[edit]Ow, OK... you marked it as resolved... I just saw that...

[MMock]

Yup - your explanation was very clear! Resolved, and I added to your rep. Thanks again!