I am using SQL Server 2014 and I have a table with a list of addresses, with items in separate columns.
For example:
st number Prefix st name suffix Dir unit number city zip
___________________________________________________________________________
1234 W Main St NULL Unit 34 LA 90210
345 NULL George Ave NULL NULL NULL SF 94525
123 E Bloom Ct W NULL NULL DC 99342
I want to concatenate these addresses and combine them into one single cell.
The main goal is to use that concatenated address to link to another table. for example, if I concatenate using ISNULL to ignore the blank values it will give me this
345 _ George Ave _ _ _ SF 94525.
(I added the _ to show where a space is used)
It adds a space when there is a null value. If there is a space the link to the other table will not work. I tried COALESCE but it didn't work. I am not too familiar with Coalesce, but I think COALESCE will give me the first non null value only. what I would like as a result is:
1234 W Main St Unit 34 LA 90210
345 George Ave SF 94525
123 E bloom Ct W DC 99342
How could I combine these items without spaces in between for the null values? Please help. Thanks
Building on Prdp's answer, a simple replace (or two) can eliminate the double/triple spaces.
Declare @YourTable table ([st number] varchar(25),Prefix varchar(25),[st name] varchar(25),suffix varchar(25),Dir varchar(25),unit varchar(25),number varchar(25),city varchar(25),zip varchar(25))
Insert Into @YourTable values
('1234','W' ,'Main' ,'St' ,NULL,'Unit','34','LA','90210'),
('345' ,NULL,'George','Ave',NULL,NULL ,NULL,'SF','94525'),
('123' ,'E' ,'Bloom' ,'Ct' ,'W' ,NULL ,NULL,'DC','99342')
Select FullAddress = replace(replace(concat([st number],' ',Prefix,' ',[st name],' ',suffix,' ',Dir,' ',unit,' ',number,' ',city,' ',zip),' ',' '),' ',' ')
From @YourTable A
Returns
FullAddress
1234 W Main St Unit 34 LA 90210
345 George Ave SF 94525
123 E Bloom Ct W DC 99342
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments