Concatenate Address field in Sql

Question

I want to concatenate a select stament for AddressLine1 up to AddressLine4, PostalCode and a PhoneNo columns in sql server 2008 such that each field will begin in a new line. This is to be use for reporting purposes. Which is the best way to have this done?

Desired outcome is:

    2 Jojo Street
    Kenyon Express Way
    Exeee
    UY19 78DF
    08945392847

Edit your question and provide sample data and desired results. — Gordon Linoff, Apr 19 '16 at 10:55
You can use `+` to concatenate varchar columns. Newline would be `CHAR(13)+CHAR(10)`. — TT., Apr 19 '16 at 10:57

score 4 · Accepted Answer · edited Aug 14 '18 at 21:37

4

You can concatenate the required fields, and use the char(13)+char(10) in between them for a new line.

select AddressLine1 + char(13)+char(10)
       + AddressLine2 + char(13)+char(10)
       + AddressLine3   +char(13)+char(10)
       + AddressLine4 + char(13)+char(10)
       + PostalCode + char(13)+char(10)
       + PhoneNo
from table1

edited Aug 14 '18 at 21:37

jpaugh

6,634
4
38
90

answered Apr 19 '16 at 10:59

Abdul Rasheed

6,486
4
32
48

Thanks all for the feedback. I used Abdul Rasheed solution. Charater return works but the returned lines are not in straight line with the first line on the address list. How do I overcome this? – UpwardD Apr 19 '16 at 11:52
Is there any blank space in front of the values? – Abdul Rasheed Apr 19 '16 at 11:59
Yes. There are blanck spaces – UpwardD Apr 19 '16 at 12:11

score 3 · Answer 2 · edited May 23 '17 at 12:09

3

You need to CONCAT the fields with the newline character of your target system, e.g. \n or CHAR(13)+CHAR(10)

eg. this could be helpful for you

edited May 23 '17 at 12:09

Community

1
1

answered Apr 19 '16 at 10:57

siax

150
7

upvoted for "of your target system", which is important to note. – underscore_d Apr 19 '16 at 11:04

score 0 · Answer 3 · answered Apr 19 '16 at 11:00

0

You can use something like the following:

select 'abc' + char(10) + 'def' + char(10) + 'ghi'

In SQL Server Management Studio you will still see the string in one line, but if you copy the value to an editor, you will see that they are devided by linebreaks.

answered Apr 19 '16 at 11:00

Tyron78

4,117
2
17
32

What if the target system/application requires CR+LF as line ending? Try pasting your result into Notepad vs Wordpad, for a simple example. – underscore_d Apr 19 '16 at 11:04
yes, you are right. I just typed down the windows way... and I did so when there were still no other answers - didn't intend to write duplicate answers. – Tyron78 Apr 19 '16 at 11:06
but that's my point: this isn't "the windows way", since Windows nearly always uses CR+LF, as exemplified by Notepad's complete inability to handle other line endings (or almost any other task!) – underscore_d Apr 19 '16 at 11:08
strange... just performed the mentioned example on a windows environment and it worked fine. Same on an Oracle instance quite some time ago. Isn't it Linux / Unix which requires the double char linebreak? – Tyron78 Apr 19 '16 at 11:11
No, it's quite the opposite. Windows uses CR+LF. Unix/Linux/etc use just LF. Well, FWIW, on Windows 7, with SQL 2008 R2 (I know...), running with Results to Grid doesn't invoke newlines in either editor, with either LF or CR+LF causing a space when copying/pasting. Putting Results to Text and pasting leads to a single line in Notepad and broken lines only in Wordpad (which is capable of handling LF-only endings). Querying with CR+LF to Text pastes properly into Notepad too. This might be somewhat version dependent, but the convention for Windows is to use CR+LF. – underscore_d Apr 19 '16 at 11:21
OK, thanks for this information! I see I have to get rid of old habbits. ;-) – Tyron78 Apr 19 '16 at 12:53

Concatenate Address field in Sql

3 Answers3