Link to home
Start Free TrialLog in
Avatar of fuze44
fuze44Flag for United States of America

asked on

How to reverse two strings in a single column?

We are running MySQL as the backend for a document management system.  Unfortunately, things were well underway before I arrived on the scene.  Now, we have have about 44,000 records in the DB that need to be fixed.  The problem is that all names were entered FIRST LAST instead of LAST, FIRST.

I've read the other posts that talk of how to select the last name, but I don't want it merely selected or even split into a second column.  I need the last name, re-inserted into the field and formated LAST, FIRST.

Also, I want to work on a backup table instead of the actual data.  Once I succeed in reversing the two names, can I simply rename the new table to replace the old one? I want to ensure there's no unique identifier that I risk screwing up that way.

Your help would be greatly appreciated.  
Avatar of nitinmehta
nitinmehta

Well, assuming that you do not have any keys/constraints defined on the name field and that you know how to "select" the first name and last name, the easier way to do it would be:

1. add a new NAME column in the table,
2. update the column with new values
3. create new table by selecting the newly added column and leaving out the old name field.
4. drop/rename the original table
5. rename the new table

If you need help in selecting the data from old column and checking the constraints on the same, it would help if you can specify the table structure here.

Hope that helps..

Nitin
ASKER CERTIFIED SOLUTION
Avatar of William Elliott
William Elliott
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fuze44

ASKER

Thanks for the replies.  Honestly, I'm in over my head on this.  I'm not familiar enough with MySQL to do this with much level of confidence.  I've acquired some books to do some prep work so I can back up my tables before trying anything.  For now, I need to ask:

nitinmehta: Is it necessary to create a new table instead of adding a field, moving data, and then deleting old field?

weellio: Can those commands be entered from the Query Browser?
the only way to know is to try it out  :)
short answer = yes. it will diplay the following

name
________
Doe, John


what i provided was a simple way to split the first name and the last name from a given variable, then patch them together as lastname, firstname.

the example 'john doe' gets turned into doe, john.

basically you input this into a function and pipe it through your database to replace all the names.
but honestly i think it would be better to have first and last name in sepereate fields. and if you want a combined field, then have that on top of the uncombined ones.
It is possible to drop the field but in some cases, it might break your code. For ex:

insert into tab_name values(123, something, 'something');

here, you are not explicitly specifying the column names so when you add the column and remove the old one, serial of column names will change and it may break such code, if there is any.

Although, there are options to specify the position of new column added into table as well, but because you say, you have absolutely no experience on mysql, i wouldn't ask you to do that.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fuze44

ASKER

Sorry about the long delay.  This was put onto the back burner and never resumed, but it is still an unresolved issue.  I'd like to keep it open for a while longer to get to try the solutions (I wear all of the hats around here).