CODEWITHSUNDEEP
sqlsql-server-2008
dtsg
dtsg

Reputation: 4468

Combine results from SQL SELECT into string

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

Upvotes: 1

Views: 791

Answers (5)

Joe G Joseph
Joe G Joseph

Reputation: 24046

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>

Upvotes: 1

Diego
Diego

Reputation: 36136

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

Upvotes: 0

Jester
Jester

Reputation: 1303

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;

Upvotes: 1

XN16
XN16

Reputation: 5869

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

Upvotes: 2

Kamal
Kamal

Reputation: 5522

In oracle I used ||

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

Syntax will be different in different database servers

Upvotes: 2

Related Questions