Splitting mysql name column in first and surname

Question

One of the columns in a table from the database is called 'name'. It contains a firstname and a surname all in one field. I need to come up with a clean, efficient way to split this single column into two.

3 columns:

Name (the firstname and surname)

Firstname(currently empty, first half of name should go here, if there is an insertion for example (Fred de Bakker), then 'Fred' should be firstname)

Surname (currently empty, second half of name should go here, if there is an insertion for example (Fred de Bakker), then 'de Bakker' should be surname)

What I need to do: Split name in half and place into firstname 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

What version of MySQL, please? – Schwern Sep 03 '20 at 16:32 — Schwern, Sep 03 '20 at 16:32

Flash Thunder · Answer 1 · 2020-09-03T16:44:00.277

Should be something like this:

UPDATE
    `my_table` AS `dest`,
    (
        SELECT
            `name`
        FROM
            `my_table`
    ) AS `src`
SET
    `dest`.`firstname` = substr(`src`.`name`, 1, char_length(`src`.`name`) - instr(reverse(`src`.`name`),' ')),
    `dest`.`surname` = substr(`src`.`name`,  char_length(`src`.`name`) - instr(reverse(`src`.`name`),' ')+2)
WHERE
    `dest`.`name` = `src`.`name`;

Please note that surename may have spaces in it, and it will still work.

score 0 · Answer 2 · answered Sep 03 '20 at 16:43

Something like this?

**Schema (MySQL v5.7)**

    CREATE TABLE Names
        (`Name` varchar(255), `Firstname` varchar(255),`Surname` varchar(255))
    ;
    
       
       
    INSERT INTO Names
        (`Name`)
    VALUES
        ('Fred de Bakker'),
        ('Fred2  Bakker2')
    ;
                                                                         
    UPDATE Names
    SET Firstname=(select substring_index(Name, " ", 1)),
        Surname=(select substring_index(Name, " ", -1));                                                                     

---

**Query #1**

    select * from Names;

| Name           | Firstname | Surname |
| -------------- | --------- | ------- |
| Fred de Bakker | Fred      | Bakker  |
| Fred2  Bakker2 | Fred2     | Bakker2 |

---

View on DB Fiddle

Splitting mysql name column in first and surname

2 Answers2