mysql - how to join or split columns VARCHAR type in same table -

mysql - how to join or split columns VARCHAR type in same table -

mysql:

i have table : id, first_name (varchar), last_name (varchar);

how convert table : id, name (varchar); ?

and

table : id, address (varchar);

convert :

table : id, street_name(varchar), house_number(varchar), room_number(varchar);

in other words, how bring together or split columns varchar type?

1- joining straitforward. if assume first table "originaltable", need 2 concat 2 fields , set in new table, named t1 example:

select t1 id, concat(first_name, last_name) name originaltable

the new table, t1, automatically created 2 fields (int , varchar).

2- splitting, provided there 3 non-empty sub-fields (street_name, house_number , room_number), can utilize next sql code:

select substring(address, 1, locate(' ', address)-1) street_name, substring( substring(address locate(' ', address)+1), 1, locate(' ', substring(address locate(' ', address)+1))-1 ) house_number, substring( substring( address locate(' ', address)+1 ) locate( ' ', substring(address locate(' ', address)+1) )+1 ) room_number t1

for details of 2 string functions (substring , locate) used here, see mysql documentation string functions: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html

mysql sql