MySQL: Split column into two

Question

I have a table with rows like this:

| Country.Number | Country |
| US.01          | USA     |
| US.02          | USA     |

I'd like to modify this to:

| Country | Number | Country |
| US      | 01     | USA     |
| US      | 02     | USA     |

Is there an easy way to do this?

Answer 1

1. use alter table - add new column.
2. update number column + Country.Number : use substring_index

Query:

UPDATE TABLE SET Number = SUBSTRING_INDEX('Country.Number', '.', -1),
Country.Number = SUBSTRING_INDEX('Country.Number', '.', 1);

3. alter table change field name of Country.Number

Answer 2

First add number column to table.

Then if all entries in Country.Number has a "." delimeting the "US" and "#" you would easily just fetch all rows from table and iterate over each row.

On each row you can use explode() function for example (others exist) to split the Country field into 2 pieces, "US" and "#" and then do an insert.