phpmyadmin move text from one field to another

问题:

I have a table with 2 fields. Email, Extra

Every entry in the email field has a name then a space and then surname. eg. Ravi Naidoo

Is there a SQL query that I would be able to run that would move the surname to the Extra Table field and then remove it from the Email field along with the space?

Eg. Email field: Ravi Naidoo

after the change would be

Email field: Ravi
Extra field: Naidoo

I know that this query removes the space:

UPDATE your_table SET email = REPLACE(email, ' ', '')

But am unsure how to move the surname text to the Extra field at the same time.

回答1:

UPDATE your_table 
    SET extra = substring_index(email, ' ', -1), 
        email = substring_index(email,' ',1);

回答2:

Let's consider you string is like

$user="[e-mail] Bikash Chhualsingh";

Separate the values.

$user_arr=explode(' ', $user, 2);

Now you can run the Sql

"UPDATE table_name
SET email = ".$user_arr[0].", Extra = ".$user_arr[1]." WHERE id=5;

Have a nice day :)