Combine results from SQL SELECT into string

Question

Say for example we have the following table:

Address Line 1 | Address Line 2 | Address Line 3 | Town | Region | Postcode

Is there a way of selecting all rows from this and then returning the row as one string e.g

AddressLine1 + AddressLine 2 + AddressLine 3 etc.....

I apologise if this is a basic question, very new to SQL

Some form of string concatenation is supported in every RDBMS. MS SQL Server uses +, Oracle uses ||, MySQL uses CONCAT(). What RDBMS are you using? — Michael Berkowski
– Michael Berkowski, Commented Aug 10, 2012 at 13:54
Are these tables linked to each other via any relation, i mean is there any ForeignKey or PrimaryKey in these tables — Apurav
– Apurav, Commented Aug 10, 2012 at 13:55

XN16 · Accepted Answer · 2012-08-10 13:55:56Z

2

For MS SQL this is what I would use:

SELECT    ([Address Line 1] + ', ' + [Address Line 2] + ', ' + [Address Line 3] + ', ' 
          + Town + ', ' + Region + ', ' + Postcode) AS Address
FROM      TableName

answered Aug 10, 2012 at 13:55

XN16

5,88916 gold badges52 silver badges76 bronze badges

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

3 Comments

dtsg Over a year ago

When i save the results as a .CSV from the output window and open in excel, they are still separated by columns? I removed the commas from your query too? Any ideas as to the issue?

XN16 Over a year ago

@john I have just tried what you described, in fact I tried without any separators, just spaces and zero length separators, then saved as a .csv from the Results screen and opened in Excel 2007. Unfortunately I can't seem to replicate your issue. The only time I get the data separated into different columns is when I have commas as I do in the example I gave above!

Apurav Over a year ago

@john, Just copy the result by right click and paste it to the excel file directly.

Kamal · Accepted Answer · 2012-08-10 13:55:48Z

2

In oracle I used ||

Select AddressLine1 || ',' || AddressLine2 from address;

Syntax will be different in different database servers

answered Aug 10, 2012 at 13:55

Kamal

5,5228 gold badges47 silver badges58 bronze badges

1 Comment

ypercubeᵀᴹ Over a year ago

The || is the standard concat operator but many DBMS support only proprietary operators, like +.

Jester · Accepted Answer · 2012-08-10 14:03:07Z

1

For MySQL you should use the CONCAT() function, so:

SELECT CONCAT( AddressLine1, ', ', AddressLine2, ', ', AddressLine3, ', ', Town, ', ', Region, ', ', Postcode ) AS LongAddress 
FROM ADDRESSES;

But if you want to avoid to add the ', ' between every field, you should use CONCAT_WS() instead:

SELECT CONCAT_WS( ', ', AddressLine1, AddressLine2, AddressLine3, Town, Region, Postcode ) AS LongAddress 
FROM ADDRESSES;

answered Aug 10, 2012 at 14:03

Jester

1,3131 gold badge14 silver badges25 bronze badges

Comments

Joe G Joseph · Accepted Answer · 2012-08-10 14:39:02Z

1

Try this: (SQL Server)

Please donot forget to use ISNULL() function, which just converts the column to '' if it is a null. If you do not do that , your entire row would be blank if any of the columns is null

select isnull([Address Line 1],'')+' '+
       isnull([Address Line 2],'')+' '+
       isnull([Address Line 3],'')+' '+
       isnull(Town,'') +' '+ 
       isnull(Region,'') +' '+ 
       isnull(Postcode,'')
from <table>

edited Aug 10, 2012 at 14:39

answered Aug 10, 2012 at 14:15

Joe G Joseph

24.2k5 gold badges58 silver badges59 bronze badges

1 Comment

Joe G Joseph Over a year ago

@podiluska: no.. I want to add a space after each column... :)

Diego · Accepted Answer · 2012-08-10 14:22:14Z

0

I realized the SQL-Server 2008 tag but just as an FYI, SQL Server 2012 implemented the CONCAT function so you could do something like CONCAT (AddressLine1, ',' , AddressLine2) and so one. And a good thing is that this function treats NULLS as empty strings so "Joe G Joseph" worries won't exist anynore on SQL 2012 :)

answered Aug 10, 2012 at 14:22

Diego

36.5k21 gold badges94 silver badges138 bronze badges

Collectives™ on Stack Overflow

Combine results from SQL SELECT into string

5 Answers 5

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

1 Comment

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related