• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 316
  • Last Modified:

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.  
  • 3
  • 2
  • 2
2 Solutions
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..

William ElliottSr Tech GuruCommented:
are there middle names? split names, hyphenated names? duplicate names?

if i am reading this correctly, then you have this part

DECLARE @FullName        VARCHAR(100)
DECLARE @FirstName       VARCHAR(100)
DECLARE @LastName        VARCHAR(100)
SET @FullName = 'John Doe'
SET @FirstName = (SELECT (LEFT(@FullName, NULLIF(CHARINDEX(' ', @FullName) - 1, -1))))
SET @LastName  = (SELECT (RIGHT(@FullName, ISNULL(NULLIF(CHARINDEX(' ', REVERSE(@FullName)) - 1, -1), LEN(@FullName)))))
select (@LastName + ', ' + @FirstName) as name

fuze44Author Commented:
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?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

William ElliottSr Tech GuruCommented:
the only way to know is to try it out  :)
short answer = yes. it will diplay the following

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.
If you are still interested in doing it this way, here is the syntax:

alter table <table_name> ADD <COLUMN_name> <column_definition>  AFTER <your name column>;

This will add the column after your name column, so when you drop the old name column, with:

alter table <table_name> DROP <your name column>;

you'll have the new column at the place of old column. Now rename the new column to old column with:

alter table <table_name> CHANGE <your name column> <NEW_COLUMN_NAME> <column_definition>;

Hope this helps...

Best Regards
fuze44Author Commented:
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).
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now