Separate the contain of a field

[aas1611]

Hi people! Looks like I never stop asking. But hey I'm in a learning phase here.

The question is now:

Suppose I have a table with field (say Table1) that looks like this:

Name
--------
John Doe

I want to separate this ('John' and 'Doe') onto different fields like this

Firstname Name
--------- --------
John Doe

How to do this? Can I do it on the same table (Table1), or should I create a new table?

 

[TonyGroves]

You would not use a separate table for that. First though, you need to be sure that all the names consist of two words, or that you have a policy for dealing with names containing more than two words.

First, add the new fields:[tt]
ALTER TABLE people
ADD
firstname CHAR(20),
lastname CHAR(20)
AFTER name;
[tt]
Then, populate the new fields:[tt]
UPDATE people
SET
firstname=SUBSTRING_INDEX(name,' ',1),
lastname=SUBSTRING_INDEX(name,' ',-1);[/tt]
If some of your names contain more than two words, you would need to change that code to suit.

Then, drop the original field (because it's redundant data):[tt]
ALTER TABLE people
DROP name;
[/tt]

 

[TonyGroves]

Sorry, the first query should have been:[tt]
ALTER TABLE people
ADD firstname CHAR(20) AFTER name,
ADD lastname CHAR(20) AFTER firstname;
[/tt]

 

[aas1611]

I got caught up with something else, so took me a while to get back to this.

I had thought of using SUBSTRING_INDEX. The problem is, sometimes names consist of two or more words. I've already tried to modify the codes (and looked for other solutions), but it hasn't come out quite good.

so under the name field, there are names like the following:

Name
----
John Doe
Jane Fonda Doe
Jennifer Aniston-Pitt

For any case, I only need the first names (John, Jane, Jennifer) as first names, and the rest as last names (Doe, Fonda Doe, Aniston-Pitt).

any solution for all of these cases?