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

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.

Answer 2

1. use alter table - add new column.

2. update number column + Country.Number : use substring_index

   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 3

After change your table shema you can use this query to fill it with data:

INSERT INTO table_with_new_scheme (
   SELECT 
      explode('.', Country.Number)[0] as CountryAbbreviation,
      explode('.', Country.Number)[1] as Number,
      Country
   FROM table_with_old_schema)