MySQL split data into two additional columns

I have a table with 3 columns, FullName, FirstName and LastName

FullName is the only column with data as it includes a full name such as John Smith with the normal space between the first and last names.

I need to get the John into the FirstName and Smith into the Lastname

Can someone show me the exact MySQL syntax to populate to the two other columns with the data from the FullName for the entire table?
pda4meAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ee_rleeConnect With a Mentor Commented:
hi

if all names only have one first name use:

SELECT substring(FullName,1, locate(' ',FullName)-1) as FirstName,  
             substring(FullName,locate(' ',FullName)+1) as LastName

for it to work with people with more that one first name (i.e. John Ryan Smith):

SELECT substring(FullName,1,length(FullName)- locate(' ',reverse(FullName))) as FirstName,  
             substring(FullName,length(FullName)- locate(' ',reverse(FullName))+ 2) as LastName
0
 
cybercookie72Commented:
just to add to Wizard's reply....the select statement returns FullName in two columns...to populate the two empty columns in your table as you described you might use an UPDATE

using Wizard's code::

UPDATE your_table_name SET FirstName = substring(FullName,1, locate(' ',FullName)-1),  
              LastName = substring(FullName,locate(' ',FullName)+1);
0
All Courses

From novice to tech pro — start learning today.