[SOLVED] Switch text around thats in the same column?

[Solarpitch]

Hey Guys,

 

I have a table that base around 3000 records of user's. The problem is I have a column called name which has the user's full name .. first name first... surname second as follows...

 

Patrick Cayne

Lisa Telford

Gerard Fitzpatrick

 

is there anyway I can run a command that will switch them around too... surname first, first name last

 

Cayne Patrick

Telford Lisa

Fitzpatrick Gerard

 

Please god let there be a sql command I can run! 

[fenway]

Don't switch them... make 2 separate fields!!!!!!!

[Solarpitch]

Believe me if I could I would.... see the system runs with an old TCL application and having the data any other way would break parts of the app. So unless I can do this I will need to update and change each individual record.. then kill myself shortly after.

 

Is there anyway of switching them?

[Maq]

Are they all in that same exact format?  Cause you could explode that column, switch them and update. 

 

*This isn't the safest way...

[fenway]

You can even do this in mysql if you are *certain* there's only ever a single space with SUBSTRING_INDEX().

[Solarpitch]

Ummm See... I have the database copied on another hosting account so it doesnt matter if I feck it up so I'll trying anything at this stage. Although some names are different.... like a few will have an 'o' like

 

Paula o Reily and so on... so not sure if there's a way around that either.

[Solarpitch]

Maq - what would exploding the column do exactly?

[fenway]

Ummm See... I have the database copied on another hosting account so it doesnt matter if I feck it up so I'll trying anything at this stage. Although some names are different.... like a few will have an 'o' like

 

Paula o Reily and so on... so not sure if there's a way around that either.

If you manually fix the ones that have extra spaces, you'll be able to handle it easily.

[Maq]

Maq - what would exploding the column do exactly?

 

For example:

 

$result = mysql_query("SELECT * FROM table") 
or die(mysql_error());  
while($row = mysql_fetch_array( $result )) {

$old_name = $row['full_name']; //take original column data (full_name)
$pieces = explode(" ", $old_name); //explode it with a space, to get the first and last name
$new_name = $pieces[1]. " " .$pieces[0]; //concatenate the pieces to create the new name
$result = mysql_query("UPDATE table SET full_name='$new_name' WHERE id = {$row['id']}") 
or die(mysql_error()); //insert it where the id is where you got it from

echo $row['id'] . " Changed from " . $old_name . " to: " . $new_name . "
"; //make sure it worked...

}

 

*** Have not tested it.  Should give you an idea.  You only have 3,000 records so this shouldn't be a problem on your resources, although if you had 10's of thousands it could put your server to its knees.

[Solarpitch]

Thanks Maq... that worked perfect. There were a few I had to update manually but was only a small few out of the 3000. Thanks

[Maq]

This really isn't the proper way to do things but if it worked it worked.  You should have deleted your old column (the full name) and created additional columns for the names according to whatever you need (first name, middle name, last name etc...).  Then you could just SET last_name = pieces[0] and first_name = pieces[1].