Can I concatenate multiple MySQL column into one field?

Question

Is it possible to concatenate billing_address_street,billing_address_city,billing_address_state,billing_address_postalcode, billing_address_country as one field called address?

Expected Result of the address field as below

3401 Dufferin St, North York, ON, M6A 2T9, Canada

SELECT a.name                       AS Name, 
       a.billing_address_street     AS Address, 
       a.billing_address_city       AS City, 
       a.billing_address_state      AS State, 
       a.billing_address_postalcode AS Zip, 
       a.billing_address_country    AS Country, 
       a.phone_office               AS PhoneNumber1, 
       a.date_modified              AS DateModify, 
       ac.store_c                   AS Store, 
       e.email_address              AS Email 
FROM   accounts AS a, 
       accounts_cstm AS ac, 
       email_addr_bean_rel AS eb, 
       email_addresses AS e 
WHERE  a.id = ac.id_c 
       AND a.id = eb.bean_id 
       AND eb.bean_id = e.id 
       AND a.phone_office = '6471111234' 
       AND a.deleted != 1 
GROUP  BY a.id 
ORDER  BY a.date_modified 
LIMIT  1

Have you tried using `CONCAT` function? http://www.mysqltutorial.org/tryit/query/mysql-concat/#1 — SS_DBA, Apr 25 '19 at 17:41

score 2 · Accepted Answer · answered Apr 25 '19 at 17:42

If you can, do this in your application layer. It's better equipped to deal with missing address components and such.

If you really want to do it in the database layer, then CONCAT() can do it:

SELECT a.name                       AS Name,
       CONCAT(
         a.billing_address_street, ',',
         a.billing_address_city, ',',
         a.billing_address_state, ',', 
         a.billing_address_postalcode, ',', 
         a.billing_address_country
       ) AS address, 
       a.phone_office               AS PhoneNumber1, 
       a.date_modified              AS DateModify, 
       ac.store_c                   AS Store, 
       e.email_address              AS Email 
FROM   accounts AS a, 
       accounts_cstm AS ac, 
       email_addr_bean_rel AS eb, 
       email_addresses AS e 
WHERE  a.id = ac.id_c 
       AND a.id = eb.bean_id 
       AND eb.bean_id = e.id 
       AND a.phone_office = '6471111234' 
       AND a.deleted != 1 
GROUP  BY a.id 
ORDER  BY a.date_modified 
LIMIT  1

thank you. That worked. I will try to do though PHP level as per your suggestion — Nick, Apr 25 '19 at 17:49
This is fine as an interim solution, but PHP (e.g. [`implode`](http://php.net/manual/en/function.implode.php)) gives you a lot more control. Remember that some regions don't have "states", or might have address lines that are ridiculous, involving 3-5 lines (looking at you, England). — tadman, Apr 25 '19 at 17:50

score 0 · Answer 2 · answered Apr 25 '19 at 17:43

0

You should be able to use CONCAT: https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_concat

possible duplicate: https://stackoverflow.com/a/5734624

answered Apr 25 '19 at 17:43

pms

944
12
28

Can I concatenate multiple MySQL column into one field?

2 Answers2