Solved

Combine address fields into one field in SQL

Posted on 2008-06-11
11
901 Views
Last Modified: 2008-06-22
I have 4 fields in My table Name, Addr1, Addr2, Addr3.

e.g.
Name:          J Smith
Addr1          37 Main Street
Addr2          Auckland
Addr3          Null

I would like to combine these into one field that reads:-

J Smith, 37 Main Street, Auckland

Is there a way to do this with a case statement it function - I would like to select this table and list one field in the format above.
0
Comment
Question by:donhannam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +4
11 Comments
 
LVL 31

Expert Comment

by:James Murrell
ID: 21757733
(name + ' ' +Addr1 +' ' etc)
0
 
LVL 31

Expert Comment

by:James Murrell
ID: 21757737
whoops forgot (name + ' ' +Addr1 +' ' etc) as name_address
0
 
LVL 8

Expert Comment

by:matrix_aash
ID: 21757744
You can achieve it witout CASE Statement by concatenating the fields as follows:

Select NAME + ',' + ' ' + Addr1 + ' ' + Addr2 +',' +  ' ' + Addr3 From Tablename

Hope this helps

Cheers.
0
Do you have a plan for Continuity?

It's inevitable. People leave organizations creating a gap in your service. That's where Percona comes in.

See how Pepper.com relies on Percona to:
-Manage their database
-Guarantee data safety and protection
-Provide database expertise that is available for any situation

 
LVL 5

Expert Comment

by:rstomar
ID: 21757802
Select Name + COALESCE(', ' + Addr1,'') + COALESCE(', ' + Addr2,'')  + COALESCE(', ' + Addr3,'')
from TableName
0
 
LVL 2

Expert Comment

by:howyue
ID: 21758206
i would come out something similar to rstomar solution which take cares of null value and comma seperator altogether.
0
 

Author Comment

by:donhannam
ID: 21758441
OK - can just concatenate but I have a large number of records - some with no values in so to create this statement I need to do something to put the comma in the right place.

i.e. if first field null or '' then don't put a comma after first field - If any other field is null or '' then don't put a comma before it.
0
 
LVL 8

Assisted Solution

by:matrix_aash
matrix_aash earned 500 total points
ID: 21758462

Select NAME + ',' + ' ' + Addr1 + ' ' + Addr2 +',' +  ' ' + Addr3,
CASE WHEN NAME IS NULL THEN (NAME + ' ' + Addr1 + ' ' + Addr2 +',' +  ' ' + Addr3),
CASE WHEN ADDR1 IS NULL THEN (NAME + ',' + ' ' + Addr1 + ' ' + Addr2 +',' +  ' ' + Addr3),...SO ON
ELSE ''
END AS O
 From Tablename
0
 
LVL 2

Expert Comment

by:MichealMcLoughlin
ID: 21758635
Select Replace(Name + ',' + Coalesce(Addr1,'') + ',' + coalesce(Addr2,'') + ',' +  Coalesce(Addr3,'') + ',' + Coalesce(Addr4,''),',,','')  'Address' From MyTable
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 21762004
Create a computed column -- one time -- on the table, like so:

ALTER TABLE tablename
    ADD NameAndAddress AS Name + COALESCE(', ' + Addr1, '') + COALESCE(', ' + Addr2, '') + COALESCE(', ' + Addr3, '')

Then use that new name like any other column, for example:

SELECT NameAndAddress
FROM ...
WHERE NameAndAddress LIKE '%...%'
ORDER BY NameAndAddress

The code assumes:
1) name is always present
2) a missing address value will be NULL not '' (empty).

If either of those are wrong, pls let me know.
0
 
LVL 5

Expert Comment

by:rstomar
ID: 21762519
thats what my solution does......did you try it
0
 

Accepted Solution

by:
donhannam earned 0 total points
ID: 21764668
Thanks for suggestions. I have had another think through and have come up with the statement below.

SELECT     Name + CASE WHEN Name IS NULL OR
                      Name = '' OR
                      Addr1 IS NULL OR
                      Addr1 = '' THEN '' ELSE ', ' END + Addr1 + CASE WHEN Addr2 IS NULL OR
                      Addr2 = '' THEN '' ELSE ', ' END + Addr2 + CASE WHEN Addr3 IS NULL OR
                      Addr3 = '' THEN '' ELSE ', ' END + Addr3 + CASE WHEN Addr4 IS NULL OR
                      Addr4 = '' THEN '' ELSE ', ' END + Addr4 + CASE WHEN Addr5 IS NULL OR
                      Addr5 = '' THEN '' ELSE ', ' END AS LongAddress
FROM         Cust_Table

This seems to work OK. In the examples given Coalesce and Replace where used. I would be interested to know the purpose of using these.

0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

617 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question