How to not add a carriage return (char(13)) if a value is null?-CodePudding

I need to figure out a way to create a column where it has address lines in the same column but after each address line there is carriage return. e.g.

123 fake street [carriage return]
Fake City [carriage return]
Fake County [carriage reutrn]

Now I do not want to add a carriage return if the fake city is null other wise you get

123 fake street [carriage return]
[carriage return]
Fake County [carriage reutrn]

This is what I have as my query:

o.ADD_1   CHAR(13)   o.ADD_2   CHAR(13)   o.ADD_3   CHAR(13)   o.ADD_4   CHAR(13)   
o.POST_CODE AS FULL_OWNER_ADDRESS_LIST

I have tried this

ISNULL(o.ADD_2 CHAR(13) , '')

But obviously it will never be null because of the char(13).

To keep it simple here is the table in use called OWNERS

Add_1 | Add_2 | Add_3 | Add_4 | Postcode

Using SQL Server.

Any ideas?

CodePudding user response：

But obviously it will never be null because of the char(13).

Not unless you have set the CONCAT_NULL_YIELDS_NULL option to OFF, which is non-standard and slated to be removed in "a future version" of SQL Server.

With that option set ON, which is the default and standards-compliant setting, Null CHAR(13) will still be Null.