I am trying to populate an Address column with a Member's full address.
The database that I am using stores Address1, Address2 (Such as an APT# or Suite#), City, State, and ZIP in separate columns as it should.
I have coded as follows:
[Member Address] = RTRIM(v.Address1) + ' ' + RTRIM(v.Address2) + ' ' + RTRIM(v.City) + ' ' + v.State + ' ' + v.ZIP,
If the Member does not have any data in Address2, it is inserting a blank to go a long with the blank space that I have already inserted to keep Address1 and Address2 spaced correctly.
Essentially, my code is working if the Member has data in both Address1 and Address2 only.
1 Main Street Apt#1 Anytown, MA 12654
2 South Street Sometown, MA 16545 (Displaying two spaces between Street and Sometown)
Does anyone have any ideas?
create table #AB_LockerRoomDrama(Address1 varchar(50),
Address2 varchar(50), City varchar(50), State varchar(50),
insert into #AB_LockerRoomDrama
select '1 Main Street', 'Apt#1', 'Anytown', 'MA', 12654 union
select '2 South Street','', 'Sometown', 'MA', 16545
select RTRIM(v.Address1) +
when len(rtrim(ltrim(v.Address2))) = 0 then ''
else ' ' + RTRIM(v.Address2)
' ' + RTRIM(v.City) +
' ' + v.State + ' ' + v.ZIP
from #AB_LockerRoomDrama v
drop table #AB_LockerRoomDrama
do the same case statement where there is a possibility the field is empty. And if you have any NULL values in any of the columns use iSNULL(column, '') otherwise concatenating with a NULL valued column will cause an error
I'd suggest handling both NULLs and blank values, like so:
[Member Address] = RTRIM(v.Address1) + ISNULL(NULLIF(' ' + RTRIM(v.Address2), ' '), '') + ISNULL(NULLIF(' ' + RTRIM(v.City), ' '), '') + ISNULL(NULLIF(' ' + v.State, ' '), '') + ISNULL(NULLIF(' ' + v.ZIP, ' '), '')
I don't see what version you are working with - but you can use CONCAT which will handle NULL values for you.
[Member Address] = CONCAT(RTRIM(v.Address1), ' ' + RTRIM(v.Address2), ' ', RTRIM(v.City), ' ', RTRIM(v.State), ' ', RTRIM(v.ZIP))