CODEWITHSUNDEEP
sqlmysql
NinjaCat
NinjaCat

Reputation: 10194

MySQL: Split column into two

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?

Upvotes: 6

Views: 8398

Answers (3)

Haim Evgi
Haim Evgi

Reputation: 125456

  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);
  1. alter table change field name of Country.Number

Upvotes: 13

Łukasz W.
Łukasz W.

Reputation: 9755

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)

Upvotes: -1

Chris
Chris

Reputation: 12078

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.

Upvotes: 1

Related Questions