Splitting a field into two fields in mySQL

Question

I have a field that contains the fullname of a user (Fullname). I'd need to split it into two other fields FirstName and LastName. Ideally with one query I'd like to be able to populate the FirstName and LastName columns from the value contained in the Fullname. Thanks.

This should help you http://stackoverflow.com/questions/1096679/can-mysql-split-a-column — Stokedout, Jan 10 '13 at 12:05
@Andrea Nagar: how you are storing the firstname and lastname as fullname, what is the separator you are using? — Suresh Kamrushi, Jan 10 '13 at 12:44

score 2 · Accepted Answer · answered Jan 10 '13 at 12:09

2

You could try (if firstname and lastname are sepparated by a whitespace):

update table_name
   set FirstName=substr(FullName,1,locate(' ',FullName)),
       LastName=substr(FullName,locate(' ',FullName)+1);

answered Jan 10 '13 at 12:09

Oscar Pérez

4,377
1
17
36

Or us [`SUBSTRING_INDEX()`](http://dev.mysql.com/doc/en/string-functions.html#function_substring-index). – eggyal Jan 10 '13 at 12:10

Splitting a field into two fields in mySQL

1 Answers1