Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

mysql

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

like image 614
jskidd3 Avatar asked Dec 02 '13 09:12

jskidd3


2 Answers

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"

like image 53
Bohemian Avatar answered Sep 28 '22 04:09

Bohemian


This improves upon the answer given, consider entry like this "Jack Smith Smithson", if you need just first and last name, and you want first name to be "Jack Smith" and last name "Smithson", then you need query like this:

-- MySQL

SELECT 
    SUBSTR(full_name, 1, length(full_name) - length(SUBSTRING_INDEX(full_name, ' ', -1)) - 1) as first_name, 
    SUBSTRING_INDEX(full_name, ' ', -1) as last_name 
FROM yourtable
like image 42
Ciantic Avatar answered Sep 28 '22 06:09

Ciantic