CODEWITHSUNDEEP
sqlmysql
Matt Elhotiby
Matt Elhotiby

Reputation: 44066

mysql - combining columns into one big column

I have a table with addresses... street address, city, state & zip I need the street address city state and zip in one column any ideas

Upvotes: 1

Views: 5714

Answers (4)

DhruvPathak
DhruvPathak

Reputation: 43235

This would help you : string-functions

Concatenating the column values with desired seperators. So an example query in your case can be :

SELECT CONCAT(street-address, '\n', city, ', ',state,'-',zip) as full_address FROM table;

Upvotes: 2

Ashraf Abrahams
Ashraf Abrahams

Reputation: 769

I wouldn't suggest you place all those columns into one column either. You might want to search by city or state or one of the other individual columns later and having them as separate fields would be much easier. You could either just concat the fields as part of a SELECT statement as suggested earlier, or better yet, if it is something that will be done frequently, you could create a VIEW with the fields concatenated and then SELECT the view, or you could simply create a Stored Function that will concat the fields and call the function as part of a SELECT statement. Something like this should work.

CREATE FUNCTION CONCAT_ADDRESS (p_id INT)
  RETURNS VARCHAR(255)
  BEGIN
  DECLARE v_street,v_city,v_state,v_zip, address VARCHAR(255);
  SELECT street_address, city, state, zip 
  INTO v_street,v_city,v_state,v_zip
  FROM table WHERE id = p_id;
  SET address = CONCAT(v_street,', ', v_city,', ', v_state,', ', v_zip);
  RETURN address;
END |

Once the function is created, you can use it anytime as part of a SELECT statement as in:

SELECT id, name, surname, CONCAT_ADDRESS(id) FROM table;

Upvotes: 2

RC.
RC.

Reputation: 28197

You can just concat them

SELECT CONCAT(address, '  ', city, ', ', state, ' ', zip) FROM table;

If you want to make this more convenient for you in the future or for other users, consider creating a view of the table that does this and provides it as a column named location or the like.

Upvotes: 6

Wige
Wige

Reputation: 3918

If you need to store the merged values in the table, use

UPDATE address SET bigcolumn = CONCAT(streetaddress, ', ', city, ', ', state, ' ', zip);

Upvotes: 2

Related Questions