Menu
I have a address in more than one column in a table.
SELECT FirstName, LastName, StreetAddress, City, Country, PostalCode FROM Client I am trying to concatenate address related columns into one filed using Comma (,) as a separator but if any of the column "eg. City" is null or empty, comma should not be there.
How to use ternary operator in TSQL like one has in c#? Or suggest me the best practice?
Thanks
485
To append a string to another and return one result, use the || operator. This adds two strings from the left and right together and returns one result. If you use the name of the column, don't enclose it in quotes. However, in using a string value as a space or text, enclose it in quotes.
Concatenate Rows Using COALESCE All you have to do is, declare a varchar variable and inside the coalesce, concat the variable with comma and the column, then assign the COALESCE to the variable. In this method, you don't need to worry about the trailing comma.
When you concatenate anything with a null, it returns null. So I'm trying to concatenate a comma with the given column value and if that expression returns null, I use Coalesce to return an empty string. At the end, if I get a value, the entire result will start with a comma. So I remove that comma using the Stuff function.
Select Stuff( Coalesce(',' + FirstName,'') + Coalesce(',' + LastName,'') + Coalesce(',' + StreetAddress,'') + Coalesce(',' + City,'') + Coalesce(',' + Country,'') + Coalesce(',' + PostalCode ,'') , 1, 1, '') From Client If you only want the address, then obviously you would only include those columns:
Select FirstName, LastName , Stuff( Coalesce(',' + StreetAddress,'') + Coalesce(',' + City,'') + Coalesce(',' + Country,'') + Coalesce(',' + PostalCode ,'') , 1, 1, '') From Client If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
