MySQL to Split a Column

Databases
1

Hi All,

Can anyone help me construct a MySQL command that will look at a column (in my case name) and select the last name out and leave the first name in the column and then add the last name to a different column.

The structure of the name column is: lastname, firstname

I would like the name column to keep the firstname but put the lastname into a new column already defined as lastname. (without the comma)

Any help would be appreciated.

Thanks,

Rick

2 
UPDATE daTable
   SET name = TRIM(SUBSTRING_INDEX(name,',',-1))
    , lastname = SUBSTRING_INDEX(name,',',1)
3

Thank you sir,

The only issue was that the code you supplied put the same value in both fields but I was able to discern what you were doing and with 2 MySQL commands was able to accomplish the task.

I need your book BTW, so I’ll order it today from Sitepoint.

Thanks again.

Rick

4

well, whaddya know, i bamboozled myself

the docs are quite clear about it –

so instead of what i gave you, it should have been the other way around:

UPDATE names
   SET lastname = SUBSTRING_INDEX(name,',',1)
     , name = TRIM(SUBSTRING_INDEX(name,',',-1));

see the difference?

:slight_smile:

5

Yes, I sure do. And I do appreciate your help.

I did order your book for my library.

The Sitepoint books are the best instructional guides on the market, I’m sure yours will be right up there with the other 11 that I have.

Thanks to all of the contributors of Sitepoint. I have grown tremendously since I discovered you guys and gals.

To Your Success,

Rick