MySQL: split a column into two

Question

I have a table with these lines:

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

I would like to change this to:

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

Is there an easy way to do this?

Answer 1

• use change table - add a new column.
• update column number + country. Number: use substring_index

Query:

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

3. change table change field name Country.Number

Answer 2

First add a column of numbers to the table.

Then, if all entries in Country.Number are set to "." dividing "US" and "#", you can easily just get all the rows from the table and go through each row.

On each line, you can use the explode () function, for example (others exist) to divide the Country field into 2 parts: "US" and "#", and then insert.

Answer 3

After modifying the shema table, you can use this query to populate 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)