Splitting a single column (name) into two (forename, surname) in SQL
mysql, sql
Solution
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.
Problem
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