15

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

jskidd3
  • 4,609
  • 15
  • 63
  • 127
  • 1
    I think stackoverflow should ask which rdbms as suggestion tag option as soon as there is sql tag. – Vishwanath Dalvi Dec 02 '13 at 09:06
  • http://stackoverflow.com/questions/2647/split-string-in-sql – ray Dec 02 '13 at 09:08
  • 1
    @ray - all the answers there appear to be either MSSQL specific, or to require features MySQL users generally lack access to (such as recursive CTEs). – TML Dec 02 '13 at 09:10
  • 1
    Maybe a duplicate question? http://stackoverflow.com/questions/14950466/how-to-split-the-name-string-in-mysql – Giupo Dec 02 '13 at 09:10
  • 1
    @Giupo Yeah, probably - I went ahead and answered it anyway, because there's a lot of additional information that a quality answer could be providing which you won't find there. – TML Dec 02 '13 at 09:12
  • How would you like to split `Ludwig van Beethoven` ? or `John F. Kennedy` ? not to speak about other _"different"_ languages/cultures ... – joop Dec 02 '13 at 09:32
  • @joop Thankfully this was not a problem otherwise I would have stated it in the question. – jskidd3 Dec 02 '13 at 12:01
  • See also https://stackoverflow.com/questions/1122328/first-name-middle-name-last-name-why-not-full-name – Raedwald Oct 01 '19 at 10:41
  • [Assuming that everyone has a forename and surname is incorrect](https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/). – Raedwald Oct 01 '19 at 10:42

8 Answers8

28

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.

fthiella
  • 48,073
  • 15
  • 90
  • 106
9

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"

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Just so you know, the answer you accepted has a potential bug; for babes like "John De Lacey", the other answer will discard all but the first and last words. Thus answer will put the first word into the forename column DVD the *rest* (however many words that is) into the lastName column, thus not losing data. – Bohemian Dec 02 '13 at 09:55
  • Thanks, I understand but all content in the 'name' column is always two words in this situation so this shouldn't be a problem. – jskidd3 Dec 02 '13 at 12:02
  • 1
    When a name is only one word, then this will set the first name and last name as the same thing – Zachary Weixelbaum Jan 19 '18 at 15:11
3

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.

Yen
  • 597
  • 1
  • 5
  • 14
3

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
Ciantic
  • 6,064
  • 4
  • 54
  • 49
1

Just wanted to share my solution. It also works with middle names. The middle name will be added to the first name.

SELECT 
TRIM(SUBSTRING(name,1, LENGTH(name)- LENGTH(SUBSTRING_INDEX(name, ' ', -1)))) AS firstname, 
SUBSTRING_INDEX(name, ' ', -1) AS lastname
Huti
  • 21
  • 1
  • Not sure why this got a down vote. It works just fine, and as stated adds the middle names to the first name and keeps the last name as the last name. If this is the desired behaviour, then this answers it perfectly. – Novocaine Jul 24 '19 at 09:50
  • Note that this breaks for names with umlauts and accents, because "Jürgen" returns LENGTH of 7. Use CHAR_LENGTH instead. – Hendrik Nov 28 '20 at 19:04
0

I had a similar problem but with Names containing multiple names, eg. "FirstName MiddleNames LastName" and it should be "MiddleNames" and not "MiddleName".

So I used a combo of substring() and reverse() to solve my problem:

select 
    SystemUser.Email, 
    SystemUser.Name, 
    Substring(SystemUser.Name, 1, instr(SystemUser.Name, ' ')) as 'First Name',
    reverse(Substring(reverse(SystemUser.Name), 1, instr(reverse(SystemUser.Name), ' '))) as 'Last Name',

I do not need the "MiddleNames" part and maybe this is not the most efficient way to solve it, but it works for me.

ErikS
  • 1
0

Got here from google, and came up with a slightly different solution that does handle names with more than two parts (up to 5 name parts, as would be created by space character). This sets the last_name column to everything to the right of the 'first name' (first space), it also sets full_name to the first name part. Perhaps backup your DB before running this :-) but here it is it worked for me:

UPDATE users SET
 name_last =  
 CASE
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 1)) = LENGTH(full_name) THEN ''
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 2)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -1)
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 3)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -2)
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 4)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -3)
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 5)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -4)
 WHEN LENGTH(SUBSTRING_INDEX(full_name, ' ', 6)) = LENGTH(full_name) THEN SUBSTRING_INDEX(del_name, ' ', -5)
ELSE ''
END,
full_name = SUBSTRING_INDEX(full_name, ' ', 1)
WHERE LENGTH(name_last) = 0 or LENGTH(name_last) is null or name_last = ''
amos
  • 23
  • 4
0

SUBSTRING_INDEX didn't work for me in SQL 2018, so I used this:

declare @fullName varchar(50) = 'First Last1 Last2'
declare @first varchar(50)
declare @last varchar(50)

select @last = right(@fullName, len(@fullName)-charindex(' ',@fullName, 1)), @first = left(@fullName, (charindex(' ', @fullName, 1))-1);

Yields @first = 'First', @last = 'Last1 Last2'

Dave Cole
  • 2,446
  • 2
  • 20
  • 26