SQL Server : concatenate values and ignore null or blank values

Question

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

If there is a space the link to the other table will not work. ... How do plan on joining a partial address with missing information, even if there are no extra spaces? — Tim Biegeleisen
– Tim Biegeleisen, Commented Jan 21, 2017 at 15:13

John Cappelletti · Accepted Answer · 2017-01-21 15:48:33Z

5

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

answered Jan 21, 2017 at 15:48

John Cappelletti

82.3k7 gold badges50 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Pரதீப் Over a year ago

I understood what Gordan was saying about my answer after looking at your answer

John Cappelletti Over a year ago

@Prdp I lean towards the concat approach. I have no idea if it is faster, but in my little head, it looks cleaner.

Pரதீப் Over a year ago

Most importantly it does implicit conversion I like that. I missed that replace part :/. As far as I have used there is no performance issue with CONCAT function like FORMAT ;)

Gordon Linoff Over a year ago

This may happen to work on the data, but it is rather difficult to formulate when you have arbitrary numbers of adjacent NULL columns.

John Cappelletti Over a year ago

@GordonLinoff if I understand your comment. It would take 6 consecutive nulls to be an issue.

|

Gordon Linoff · Accepted Answer · 2017-01-21 23:41:33Z

2

You can do:

select (coalesce(st_number + ' ', '') +
        coalesce(prefix + ' ', '') +
        coalesce(st_name + ' ', '') +
        . . .
       )

That leaves a remaining space at the end of the string. You can remove it using rtrim().

select rtrim(coalesce(st_number + ' ', '') +
             coalesce(prefix + ' ', '') +
             coalesce(st_name + ' ', '') +
             . . .
            )

As Prdp points out, this assumes that the columns are actually strings. Otherwise, explicit conversion will be needed for those columns (or you can use the concat() function instead of +).

edited Jan 21, 2017 at 23:41

answered Jan 21, 2017 at 15:12

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Tim Biegeleisen Over a year ago

You should comment that, e.g., st_number + ' ' is NULL if the column itself is NULL.

Pரதீப் Over a year ago

Also this considers columns are implicitly convertible to datatype with highest precedence. When the datatype is of numeric then empty string and space in Coalesce will be converted to 0

Collectives™ on Stack Overflow

SQL Server : concatenate values and ignore null or blank values

2 Answers 2

6 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related