Unwanted Space

1

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.

Example:

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?

Thanks.

2 
create table #AB_LockerRoomDrama(Address1 varchar(50), 
Address2 varchar(50), City  varchar(50), State  varchar(50), 
zip  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) + 
       case 
	     when len(rtrim(ltrim(v.Address2))) = 0 then ''
		 else ' ' + RTRIM(v.Address2)
	   end  + 
	   ' ' + 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

3

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, ' '), '')

4

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))