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...

Answer 1

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

OUTPUT

Text 1
Text 2