mysql - combining columns into one big column

开发者_JS百科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

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.

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

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

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;

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;