Splitting a single column (name) into two (forename, surname) in SQL

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.

For the people who wants to handle fullname: John -> firstname: John, lastname: null

SELECT 
if( INSTR(`name`, ' ')=0, 
    TRIM(SUBSTRING(`name`, INSTR(`name`, ' ')+1)), 
    TRIM(SUBSTRING(`name`, 1, INSTR(`name`, ' ')-1)) ) first_name,
if( INSTR(`name`, ' ')=0, 
    null, 
    TRIM(SUBSTRING(`name`, INSTR(`name`, ' ')+1)) ) last_name

It works fine with John Doe. However if user just fill in John with no last name, SUBSTRING(name, INSTR(name, ' ')+1)) as lastname will return John instead of null and firstname will be null with SUBSTRING(name, 1, INSTR(name, ' ')-1).

In my case I added if condition check to correctly determine lastname and trim to prevent multiple spaces between them.

Try this:

insert into new_table (forename, lastName, ...)
select
  substring_index(name, ' ', 1),
  substring(name from instr(name, ' ') + 1),
  ...
from old_table

This assumes the first word is the forename, and the rest the is lastname, which correctly handles multi-word last names like "John De Lacey"