Answer a question

Currently I'm working on a database redesign project. A large bulk of this project is pulling data from the old database and importing it into the new one.

One of the columns in a table from the old database is called 'name'. It contains a forename and a surname all in one field (ugh). The new table has two columns; forenames and surname. I need to come up with a clean, efficient way to split this single column into two.

For now I'd like to do everything in the same table and then I can easily transfer it across.

3 columns:

• Name (the forename and surname)
• Forename (currently empty, first half of name should go here)
• Surname (currently empty, second half of name should go here)

What I need to do: Split name in half and place into forename and surname

If anyone could shed some light on how to do this kind of thing I would really appreciate it as I haven't done anything like this in SQL before.

Database engine: MySQL
Storage engine: InnoDB

Answers

A quick solution is to use SUBSTRING_INDEX to get everything at the left of the first space, and everything past the first space:

UPDATE tablename
SET
  Forename = SUBSTRING_INDEX(Name, ' ', 1),
  Surname = SUBSTRING_INDEX(Name, ' ', -1)

Please see fiddle here. It is not perfect, as a name could have multiple spaces, but it can be a good query to start with and it works for most names.