Skip to content
Advertisement

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

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?

Advertisement

Answer

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.

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement