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
.