how to add new line in mysql concat?

Question

Here I have attach my query. I want to display address, line by line .

   SELECT 
    tp.acct_name,
    tp.acct_no,
    tp.acct_type,
    tp.sub_type,
    tp.eci_acct_no AS bs_ship_fwd_no,
    tp.ecifwno AS bs_cons_no,
    tp.ssline_number,
    sc.code AS sales_person,
    tp.disabled,
    CONCAT(IF(ca.address1 IS NULL OR ca.address1 = '',
                ' ',
                ca.address1),
            ', ',
            IF(ca.city1 IS NULL OR ca.city1 = '',
                ' ',
                CONCAT('City:', ca.city1)),
            ' ',
            IF(ca.state IS NULL OR ca.state = '',
                ' ',
                CONCAT('State:', ca.state)),
            ' ',
            IF(cy.codedesc IS NULL OR cy.codedesc = '',
                ' ',
                CONCAT('Country:', cy.codedesc)),
            ' ',
            IF(ca.zip IS NULL OR ca.zip = '',
                ' ',
                CONCAT('Zip:', ca.zip)),
            ' ',
            IF(ca.phone IS NULL OR ca.phone = '',
                ' ',
                CONCAT('PH#:', ca.phone))) AS address,
    ca.city1 AS city,
    ca.state,
    cy.codedesc AS country,
    ca.zip,
    ca.contact_name,
    ca.phone,
    ca.fax,
    ca.email1,
    gi.poa,
    gi.fw_fmc_no,
    CONCAT(cr.codedesc, '-', cr.id) AS credit_status,
    ar.credit_limit,
    tp.forward_account
FROM
    (SELECT 
        acct_name,
            acct_no,
            acct_type,
            sub_type,
            eci_acct_no,
            ecifwno,
            ssline_number,
            disabled,
            forward_account
    FROM
        trading_partner
    WHERE
        (acct_no LIKE '080STU0001%' OR acct_name LIKE '080STU0001%' OR search_acct_name LIKE '080STU0001%') AND (acct_type LIKE '%S%' OR (acct_type LIKE '%V%' AND sub_type = 'Forwarder') OR acct_type LIKE '%O%' OR acct_type = 'C')
    ORDER BY acct_no
    LIMIT 50) AS tp
        JOIN
    cust_address ca ON (tp.acct_no = ca.acct_no AND ca.prime = 'on')
        LEFT JOIN
    genericcode_dup cy ON ca.country = cy.id
        LEFT JOIN
    cust_general_info gi ON tp.acct_no = gi.acct_no
        LEFT JOIN
    genericcode_dup sc ON gi.sales_code = sc.id
        LEFT JOIN
    cust_accounting ar ON tp.acct_no = ar.acct_no
        LEFT JOIN
    genericcode_dup cr ON ar.credit_status = cr.id
GROUP BY tp.acct_no

I need output in following format. Please Help

1771 EAST 9TH AVENUE,
CITY:AMPA,STATE:FL,COUNTRY:UNITED STATES,
ZIP:33605

Thanks in advance...

Displaying and formatting the output is the job of the client! Return a column for each of that pieces of information, and let the client display it. You have no guarantee that a `chr(13)` is displayed everywhere the same way. E.g. a browser will just ignore it (it will need a `
`), a printer or a terminal session might not stay in the right column, some clients will need an escaped `\r` or `\n`, ... — Solarflare, Jun 17 '16 at 10:11
Possible duplicate of [New line in Sql Query](http://stackoverflow.com/questions/1085662/new-line-in-sql-query) — Varun, Jun 17 '16 at 10:12
I've removed the SQL Server tag, as MySQL and SQL Server are different products. They often require different solutions. — David Rushton, Jun 17 '16 at 11:27

score 10 · Answer 1 · answered Jun 17 '16 at 10:06

10

You can use CHAR(13) to do that.
e.g. PRINT 'Text 1' + CHAR(13) + 'Text 2'

OUTPUT

Text 1
Text 2

answered Jun 17 '16 at 10:06

Sujeet Sinha

2,417
2
18
27

its working . the query as follows Within Concat using char (13).my be this is useful for some one CONCAT(IF(ca.address1 IS NULL OR ca.address1 = '',' ',ca.`address1`),'', CHAR(13),IF(ca.city1 IS NULL OR ca.city1 = '',' ',CONCAT(ca.city1)), ' , ',IF(ca.state IS NULL OR ca.state = '',' ',CONCAT(ca.state)),' , ', IF(cy.codedesc IS NULL OR cy.codedesc = '',' ',CONCAT(cy.codedesc)),' ',CHAR(13),IF(ca.zip IS NULL OR ca.zip = '',' ',CONCAT(ca.zip)),' ',IF(ca.phone IS NULL OR ca.phone = '', ' ',CONCAT('PHONE ', ca.phone) )) AS address – user123456 Jun 17 '16 at 11:37
This should be marked as the correct answer. Also, I found that CHAR(10) is the correct newline for MacOS/BSD/Linux. – Blisterpeanuts Dec 16 '20 at 15:26

Varun · Answer 2 · 2016-06-17T10:14:17.823

6

Use CHAR(13) + CHAR(10) to bring the address in new line.

edited Jun 17 '16 at 10:14

answered Jun 17 '16 at 10:06

Varun

241
1
9

score 1 · Answer 3 · answered Jun 17 '16 at 10:10

1

Can you please use CONCAT_WS in mysql. eg. select concat_ws(' ','Hello','tes','new1');

answered Jun 17 '16 at 10:10

Dhaval Bhavsar

495
5
17

how to add new line in mysql concat?

3 Answers3